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.

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

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.

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

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)

Previous Post
SQLAuthority News – #TechEdIn – TechEd India 2012 Memories and Photos
Next Post
SQLAuthority News – Download SQL Azure Labs Codename “Data Explorer” Client

Related Posts

6 Comments. Leave new

  • 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

    Reply
  • Have anyone copied this sp from 2012 to 2008 and make it works in 2008? I need it in 2008 :-(

    Reply
  • but this (dm_exec_describe_first_result_set_for_object )is not working for dynamic queries, please advice me if any other option s there

    Reply
  • I found this DMV a week ago, and I found it really useful to find any defunct stored procedures in a database. I’m making use of error_message, error_type, error_type_desc columns to find the error message and filtering down to error_type = 2 (syntax errors).

    It is really useful, in a sense ‘error_message’ tells us exactly why the sproc failed to compile.

    Reply
  • Naveen,

    There’s a problem with using the dm_exec_describe_first_result_set* functions for that purpose…it will miss some bad SPs because it will be unable to process any script blocks that use Temp tables or calls some system SPs like sp_send_dbmail. As soon as any of those things are seen in the script, these functions essentially shrug and say “we can’t do that”.

    Reply

Leave a Reply

Menu