Describe a Result Set Without Executing Its Query

I describe a result set to review a query contract before asking for its data. SQL Server analyzes the batch text and reports its columns. This shape check still involves server-side analysis and permission checks.

Describe a result set with explicit projections

The example describes three named expressions. An integer identifier, a short Unicode label and a nullable decimal amount make the intended contract visible.

The query inside the Unicode string is the subject of analysis. The outer SELECT reads metadata from sys.dm_exec_describe_first_result_set; it doesn’t request the three business values from that inner query. I keep the projection explicit so a reviewer can connect each described column to its expression.

SELECT CAST(column_ordinal AS int) AS ColumnOrdinal,
       CAST(name AS nvarchar(128)) AS ColumnName,
       CAST(system_type_name AS nvarchar(256)) AS SqlType,
       CAST(is_nullable AS bit) AS IsNullable,
       CAST(max_length AS smallint) AS MaxBytes
FROM sys.dm_exec_describe_first_result_set(
 N'SELECT CAST(7 AS int) AS ItemId,
          CAST(N''blue'' AS nvarchar(12)) AS Label,
          CAST(NULL AS decimal(9,2)) AS Amount;', NULL, 0)
WHERE is_hidden=0
ORDER BY column_ordinal;

SELECT CAST(column_ordinal AS int) AS ColumnOrdinal,
       CAST(name AS nvarchar(128)) AS ColumnName,
       CAST(system_type_name AS nvarchar(256)) AS SqlType,
       CAST(is_nullable AS bit) AS IsNullable,
       CAST(max_length AS smallint) AS MaxBytes
FROM sys.dm_exec_describe_first_result_set(
 N'SELECT CAST(@Value AS decimal(9,2)) AS Amount;',
 N'@Value decimal(9,2)', 0)
WHERE is_hidden=0
ORDER BY column_ordinal;

Read the shape rather than sample values

The expected first metadata grid contains ItemId as int, Label as nvarchar(12), and Amount as decimal(9,2). MaxBytes is a storage-length descriptor, not the length of a particular displayed value.

The label has a 24-byte maximum, while its example text contains only four characters. IsNullable should be 1 for Amount, because the query casts NULL to decimal, and 0 for ItemId and Label, which are fixed constants. The flag describes what a column can hold. It doesn’t count missing business values.

Declare parameters without supplying sample values

The second example describes a parameterized amount expression. Its declaration string gives @Value a type, rather than assigning a value. This is different from replacing parameters with convenient constants before inspection.

A constant might hide nullable input or change the inferred contract. I review the parameter names and types declared by the application. Then I compare the metadata with the consumer’s expectations.

Keep the analysis limits visible

Describing the first result has limits. Relevant dynamic SQL, temporary tables and conflicting possible first results can prevent analysis. A failure to describe the batch isn’t evidence that executing it is safe.

The function also requires permission to execute the supplied batch. This example uses simple SELECT expressions. It needs no application tables, permanent objects or private data. No temporary-table workaround is required.

Review changes at the consumer boundary

A report or API can depend on column order, aliases and precision. Nullable behavior also matters beyond the broad type family. A new alias can therefore matter even when the underlying calculation remains valid.

I compare the described contract with the reader’s binding rules, including how it handles NULL. I keep a separate execution test for actual behavior. Metadata inspection doesn’t prove correctness or measure performance.

Keep schema changes and execution separate

The analysis is a statement about the batch and the schema available when it is inspected. Later schema changes can invalidate the assumptions behind that review.

Retain the exact query text alongside its parameter declarations. For deployment, refresh the shape check against the intended target. Inspect the metadata against the intended schema, then test the query separately for its actual rows and error handling.

Look at the shape first, then run the query when you are ready.

Metadata inspection is not a runtime test, it is a review of the query’s result contract.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.