SQL Server’s hidden gem for SQL query validation

Photo by THLT LCX on Unsplash

When building dynamic SQL pipelines, metadata-driven ETL systems, or AI-powered query generators, it’s often essential to analyze and validate SQL queries before execution. But how do you check what a SQL statement will return — without actually running it?

Enter **sys.dm_exec_describe_first_result_set,** a powerful and underused dynamic management function in SQL Server.

What is sys.dm_exec_describe_first_result_set?

sys.dm_exec_describe_first_result_set is a system function in SQL Server that returns metadata about the first result set of a T-SQL batch, stored procedure, or dynamic SQL query without executing it.

It’s like asking SQL Server: “If I were to run this query, what columns, types, and metadata would I get?”

Use Cases

1: Validate SQL syntax without executing the query

2: Introspect the structure of dynamic SQL queries

3: Auto-generate destination tables for staging or analysis

4: Support LLM or AI workflows that generate SQL dynamically

5: Prevent runtime errors in ETL and reporting pipelines

Basic Example

SELECT * 
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT id, name FROM table', 
    NULL, 
    0
);

Output:

Returns metadata about the columns: id (int), name (nvarchar), and more

What If the SQL is Invalid?

If your SQL has a typo or refers to missing objects, the function will not throw a normal SQL error — instead, the output will include error metadata in fields like:

• error_number
• error_message

SELECT * 
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT bad_column FROM unknown_table', 
    NULL, 
    0
);

Output

Wrapping It in a Function for SQL Validation

CREATE FUNCTION dbo.fn_validate_sql_syntax
(
    @sql NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @error_message NVARCHAR(MAX) = NULL;
 DECLARE @count INT = 0;

    SELECT @error_message = STRING_AGG(error_message, '; ')
    ,@count = COUNT(1)
    FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0)
    --WHERE error_number IS NOT NULL;

 IF @count = 0
 BEGIN
  SET @error_message = 'No executable query exists'
 END

    RETURN @error_message;  -- NULL means valid, otherwise concatenated error messages
END

A simple method to check query syntax and structure in SQL Server — returning NULL for valid SQL or a detailed error message for invalid queries.

If you liked this article, say “Hi” on LinkedIn. Need any assistance? Feel free to reach out.

https://www.linkedin.com/in/bharatharcotbabu/

THANK YOU!