Describe the result of a query or procedure

We need to integrate a query into an application, but we don’t know for sure what names, types, and nullability it will return. Running it with real data can be expensive or require parameters that we do not yet have prepared.

What we need to distinguish

A result set is the tabular group of rows and columns returned by a statement. Its contract defines the shape a consumer can expect.

We will ask SQL Server to analyze the first possible result set without executing the query. The description allows you to review the tabular contract; it does not represent actual rows nor does it guarantee that all execution paths produce the same result.

The tabular contract describes the columns that the consumer will receive: name, position, type and possibility of containing NULL. It does not include cardinality, that is, the number of rows that a particular execution will return.

Query

/*
    Antes de ejecutar:
        Revisa la base activa, los nombres de objetos, los parámetros y los permisos.
        Adapta el ejemplo a tu modelo y pruébalo primero en un ambiente controlado.
*/

SET NOCOUNT ON;
GO
-- Descripción de una consulta
DECLARE @Consulta NVARCHAR(MAX) = N'
    SELECT
        c.IdCliente,
        c.Nombre,
        c.Correo,
        COUNT_BIG(p.IdPedido) AS CantidadPedidos
    FROM dbo.Cliente AS c
    LEFT JOIN dbo.Pedido AS p
        ON p.IdCliente = c.IdCliente
    WHERE c.Activo = @Activo
    GROUP BY
        c.IdCliente,
        c.Nombre,
        c.Correo;';

DECLARE @Parametros NVARCHAR(4000) = N'@Activo bit';

SELECT
    r.column_ordinal AS Posicion,
    r.name AS Columna,
    r.system_type_name AS TipoDevuelto,
    r.is_nullable AS PuedeSerNula,
    r.source_schema AS EsquemaOrigen,
    r.source_table AS TablaOrigen,
    r.source_column AS ColumnaOrigen,
    r.is_identity_column AS EsIdentity,
    r.is_computed_column AS EsCalculada,
    r.error_number AS NumeroError,
    r.error_message AS MensajeError
FROM sys.dm_exec_describe_first_result_set
(
    @Consulta,
    @Parametros,
    0
) AS r
ORDER BY r.column_ordinal;
GO

-- Descripción de un procedimiento
EXEC sys.sp_describe_first_result_set
    @tsql = N'EXEC dbo.ConsultarPedidos @IdCliente = @Cliente;',
    @params = N'@Cliente int',
    @browse_information_mode = 0;
GO

Parameters or permissions

Include in @Parameters each parameter used by the batch, with the same name and a compatible type. The query is parsed on the current basis; The names of two parts, permissions and temporary objects, depend on that context. Sufficient permissions are required to compile and query referenced objects. The visibility of source columns can also be limited by metadata permissions.

How to read the result

Position, Column and ReturnType allow you to build the order and type of the contract. MayBeNull describes what the parser can infer, which may be more conservative than the functional rule known to the application. Source columns are populated when SQL Server can trace them. An aggregate or calculated expression may not have a direct source column. If the batch cannot determine a compatible first result, check ErrorNumber and ErrorMessage. Conditional paths with different columns, dynamic SQL, and some temporary tables can prevent a stable description.

What this does establish

Allows you to anticipate names, order, types, and other properties of the first result set that SQL Server can infer without running the batch.

What this still does not establish

It does not return data, times, cardinality or actual execution plan. It also does not describe a second set of results and does not guarantee that a dynamic route constructed during execution preserves the observed contract.

Next reasonable check

Compare the description with the model expected by the application. It then runs a controlled test with representative parameters and validates null values, conversions, and empty sets.

Search SQL Server