SQL Server 2012 introduced a new system stored procedure called sp_describe_first_result_set, which provides a quick and easy way to retrieve information about the columns used in a stored procedure. However, there is another lesser-known DMV called sys.dm_exec_describe_first_result_set_for_object that serves the same purpose.
When working with SQL Server, it’s important to stay updated with the latest features and functionalities. Often, we miss out on learning about these new additions and end up being surprised when someone demonstrates them to us. In this blog post, we will explore the sys.dm_exec_describe_first_result_set_for_object DMV and see how it can be useful in our day-to-day SQL Server tasks.
Let’s start with a quick example. Suppose we have a stored procedure called CompSP in the AdventureWorks database, which selects columns from the HumanResources.Department table:
USE [AdventureWorks]
GO
ALTER PROCEDURE [dbo].[CompSP]
AS
SELECT [DepartmentID] id, [Name] n, [GroupName] gn
FROM [HumanResources].[Department]
GO
Now, let’s run the following two queries using the sys.dm_exec_describe_first_result_set_for_object DMV:
Option 1: Pass the second parameter @include_browse_information as 0.
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID('[dbo].[CompSP]'), 0) AS Table1
GO
Option 2: Pass the second parameter @include_browse_information as 1.
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID('[dbo].[CompSP]'), 1) AS Table1
GO
Upon examining the results, you will notice that there is no difference in the column names returned by both options. However, if you scroll to the right side of the resultset, you will see a clear difference in some columns. In the second resultset, columns like source_database and source_schema report the original table instead of NULL values.
The @include_browse_information parameter determines whether the DMV should provide details about the underlying table. When set to 1, it includes this additional information, which can be helpful in certain scenarios.
Although I have just discovered this DMV, I am yet to use it in production code. However, I can already think of a few potential use cases. For example, it can be useful when analyzing the structure of a stored procedure and understanding its dependencies on underlying tables. It can also be handy when troubleshooting performance issues related to stored procedures.
Overall, the sys.dm_exec_describe_first_result_set_for_object DMV is a valuable tool for SQL Server developers and administrators. It provides a convenient way to retrieve metadata about stored procedures and their underlying tables. By leveraging this DMV, we can gain insights into our database structures and optimize our SQL Server queries.
Do you have any ideas on how this DMV can be helpful in your SQL Server tasks? Share your thoughts in the comments below!