Monday, April 27, 2015

SQL dm_exec_describe_first_result_set dynamic management function

Here's how to call the dm_exec_describe_first_result_set dynamic management function.

This works for SQL 2012 through current version.

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

This dynamic management function takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.

This is a really useful function if you ever need to dynamically figure out the metadata that will be returned for a given stored procedure. For example, if you just need to figure out the column name and data type of each column in the result set, you can do the following:

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

For specific syntax, arguments, and what data is returned visit MSDN.

Matt Pavey is a Microsoft Certified software developer who specializes in ASP.Net, VB.Net, C#, AJAX, LINQ, XML, XSL, Web Services, SQL, jQuery, and more. Follow on Twitter @matthewpavey