SQL Server Portal

In this article we will discuss the new dynamic management function namely “sys.dm_exec_describe_first_result_set”. The functionality of this function is almost similar  to “sp_describe_first_result_set” system stored procedure, but the implementation (Syntax) and usage is different.

Syntax :

sys.dm_exec_describe_first_result(@tsql, @params, @include_browse_information)

“sp_describe_first_result_set” provides the detailed meta data on the basis of different@include_browse_information parameters. But lets say if we need only two columns from this meta data info and you would like to query it like a table, both requirements are not fulfilled by “sp_describe_first_result_set” stored procedure, because it does not return the data as a tabular form and also you cannot query only few columns. In this case you can use “sys.dm_exec_describe_first_result_set” function to fulfill both your requirements in addition to the “sp_describe_first_result_set” functionality.

Let me explain the difference with the help of simple example :

---sp_describe_first_result_set
USE AdventureWorks2012;
GO
EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM HumanResources.Department'

---sys.dm_exec_describe_first_result_set
USE AdventureWorks2012;
GO
SELECT [column_ordinal],[name],[system_type_id]   FROM sys.dm_exec_describe_first_result_set
(N'SELECT * FROM HumanResources.Department', null, 0) ;

--Output

Summary :
This function has the same functionality as “sp_describe_first_result_set” but the two main differences are given below :

1. This function can filter the meta data column as per the requirement.
2. This function returns the data in a tabular form, so you can utilize it in any other function or procedure.

Reference : MSDN