SQL Server 2011 “Denali” introduces couple of new procedures which enable you to read query metadata without executing it. Previous versions of SQL Server support reading metadata from system catalog but it is possible only for fixed objects. With following new procedures you can do similar thing but for dynamic T-SQL query.

There are 3 new procedures:

• sys.dm_exec_describe_first_result – this procedure returns column of first result set in query. Query is passed to procedure as a dynamic SQL.
• sys.dm_exec_describe_first_result_set_for_object – do the same thing as previous but for specific object_id
• sp_describe_undeclared_parameters – this procedure pulls parameters from SQL query and return them in result set

See following example for sys.dm_exec_describe_first_result:

DECLARE @sql VARCHAR(500)
SET @sql =
'USE AdventureWorks
SELECT e.LoginID, c.FirstName, c.LastName FROM HumanResources.Employee e
JOIN Person.Contact c ON c.ContactID = e.ContactID'
SELECT * FROM sys.dm_exec_describe_first_result_set (@sql, null, 0) ;

…will result in grid with LoginID, FirstName and LastName columns and their metadata:

Here is sample for sp_describe_undeclared_parameters:

DECLARE @sql NVARCHAR(MAX)
SET @sql =
'USE AdventureWorks
SELECT e.LoginID, c.FirstName, c.LastName FROM HumanResources.Employee e
JOIN Person.Contact c ON c.ContactID = e.ContactID
WHERE FirstName LIKE @firstName'
EXEC sp_describe_undeclared_parameters @tsql= @sql

…will result in grid describing @firstName parameter:

Those procedures might be very handy when you’re dealing with dynamic SQL but there are a lot of limitations. Please read Books Online for more details.

Tags: built-in functions, denali, dynamic sql, metadata, SQL Server, sql server 2011