SQL SERVER – DMV sys.dm_exec_describe_first_result_set_for_object – Describes the First Result Metadata for the Module

Here is another interesting follow up blog post of SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012. While I was writing earlier blog post I had come across DMV sys.dm_exec_describe_first_result_set_for_object as well. I found that SQL Server 2012 is providing all this quick and new features which quite often we miss  to learn it and when in future someone demonstrates the same to us, we express our surprise on the subject.

DMV sys.dm_exec_describe_first_result_set_for_object returns result set which describes the columns used in the stored procedure. Here is the quick example. Let us first create stored procedure.

USE [AdventureWorks] GO
ALTER PROCEDURE [dbo].[CompSP] AS
SELECT [DepartmentID] id
,[Name] n
,[GroupName] gn
FROM [HumanResources].[Department] GO

Now let us run following two DMV which gives us meta data description of the stored procedure passed as a parameter.

Option1: Pass second parameter @include_browse_information as a 0.

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object (
OBJECT_ID('[dbo].[CompSP]'),0) AS Table1
GO

Option2: Pass second parameter @include_browse_information as a 1.

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object (
OBJECT_ID('[dbo].[CompSP]'),1) AS Table1
GO

Here is the result of Option1 and Option2.

If you see the result, there is absolutely no difference between the results. Both of the resultset are returning column names which are aliased in the stored procedure. Let us scroll on the right side and you will notice that there is clear difference in some columns.

You will see in second resultset source_database, Source_schema as well few other columns are reporting original table instead of NULL values. When @include_browse_information result is set to 1 it will provide the columns details of the underlying table. I have just discovered this DMV, I have yet to use it in production code and find out where exactly I will use this DMV. Do you have any idea? Does any thing comes up to your mind where this DMV can be helpful.

Reference: Pinal Dave (https://blog.sqlauthority.com)