I might have said this earlier many times but I will say it again – SQL Server never stops to amaze me. Here is the example of it sp_describe_first_result_set. I stumbled upon it when I was looking for something else on BOL. This new system stored procedure did attract me to experiment with it. This SP does exactly what its names suggests – describes the first result set. Let us see very simple example of the same. Please note that this will work on only SQL Server 2012.

EXEC sp_describe_first_result_set
N'SELECT * FROM AdventureWorks.Sales.SalesOrderDetail', NULL, 1
GO

Here is the partial resultset.

Now let us take this simple example to next level and learn one more interesting detail about this function.

First I will be creating a view and then we will use the same procedure over the view.

USE AdventureWorks
GO
CREATE VIEW dbo.MyView
AS
SELECT [SalesOrderID] soi_v
,[SalesOrderDetailID] sodi_v
,[CarrierTrackingNumber] stn_v
FROM [Sales].[SalesOrderDetail]
GO

Now let us execute above stored procedure with various options. You can notice I am changing the very last parameter which I am passing to the stored procedure.This option is known as for browse_information_mode.

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 0;
GO
EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 1;
GO
EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 2;
GO

Here is result of all the three queries together in single image for easier understanding regarding their difference.

You can see that when BrowseMode is set to 1 the resultset describes the details of the original source database, schema as well source table. When BrowseMode is set to 2 the resulset describes the details of the view as the source database.

I found it really really interesting that there exists system stored procedure which now describes the resultset of the output.

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