sys.dm_exec_describe_first_result_set: Getting a Stored Procedure's Result Metadata

Matt Pavey

If you ever need to work out, at runtime, what columns a stored procedure is going to return, SQL Server has a dynamic management function for exactly that.

Available in SQL Server 2012 and later.

SELECT *
FROM   sys.dm_exec_describe_first_result_set('StoredProcedureName', NULL, 1)

It takes a Transact-SQL statement as a parameter and describes the metadata of the first result set that statement would produce, without executing it.

That is genuinely useful when you are building something generic. If all you need is the column name and data type of each column in the result set:

SELECT 'Property' = name,
       'SqlType'  = system_type_name
FROM   sys.dm_exec_describe_first_result_set('StoredProcedureName', NULL, 1)

Two related objects worth knowing about: sys.dm_exec_describe_first_result_set_for_object takes an object id instead of a statement, and sp_describe_first_result_set does the same job as a stored procedure rather than a function.

For the full syntax, arguments, and the complete list of columns it returns, see the Microsoft documentation.