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.
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
This function has the same functionality as “sp_describe_first_result_set” but the two main differences are given below :
- This function can filter the meta data column as per the requirement.
- This function returns the data in a tabular form, so you can utilize it in any other function or procedure.
Reference : MSDN