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 (http://blog.sqlauthority.com)

About these ads

4 thoughts on “SQL SERVER – DMV sys.dm_exec_describe_first_result_set_for_object – Describes the First Result Metadata for the Module

  1. Hi Pinal
    Would you know if there is a dmv or similar in 2008 that will detail the alias names of a select stored procedure?

    Thanks

    Ed

  2. Pingback: SQL SERVER – A Quick Look at Logging and Ideas around Logging « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s