T-SQL 2012 Procedure sp_describe_first_result_set

SQL Server 2012 adds a new stored procedure called sp_describe_first_result_set.  This new procedure returns metadata for the result set returned from a query. The metadata is information about what the results will look like.  sp_describe_first_result_set is an alternative to sp_columns, and appears to perform much better than sp_columns. The information here has been extracted from my SQL Saturday presentation on Whats New in TSQL 2012.

Setting up the database

For the purpose of this demo, we will be using the following database called tsql2012 from my SQL Saturday presentation on Whats New in TSQL 2012.  If you don’t want to use the tsql2012 database, you can use any database you like.

USE [Master];

CREATE DATABASE [tsql2012];

GO

USE [tsql2012];

Once the database is established we can run the following queries to set up a test table to work with, then we run a select statement to view the table contents:

CREATE TABLE MyTable
(
[DepartmentID] int,
[Revenue] int,
[Year] int,
[Name] varchar(1024)
);

insert into MyTable
values (1,10030,1998, 'xyz'), (2,10,1999, '10'), (3,20,2000, '20 Things');

SELECT * FROM MyTable;

We can see the results are pretty simple, 3 rows with 4 columns from a very simple table.

Calling sp_describe_first_result_set

Next lets take a look at the most simple form of the sp_describe_first_result_set procedure. Here we are passing in a single NVARCHAR parameter containing a query.

exec sp_describe_first_result_set N'SELECT * FROM MyTable';

In the above result set there are many columns returned, scrolling to the right will show that many of the columns are null, we will come back to that in a minute.  Here you can see several columns with various details about the results that would be returned from the query. We can see the sizes, the types, the precision, collation, and several other details.

Parameterized Queries

Next lets expand on the basic function with the parameterized query version of the stored procedure call.

[SQL]
– sp_describe_first_result_set with parameters
exec sp_describe_first_result_set
@tsql = N’SELECT * FROM MyTable WHERE Name = @name’,
@params = N’@name varchar(1024)’;

[/SQL]

In this example the results returned were exactly the same as the previous example, however the parameters passed into the stored procedure allowed for parameterized queries. Specifically specifying in this case that the @name parameter is a varchar(1024) type.

Browse_Information_Mode

Next we add a third parameter called browse_information_mode which is used to specify the columns and result type to be included in the output. There are 3 options for this parameter; 0 for no additional data, 1 to analyze for browse which includes additional results, and 2 meaning to analyze as though it was preparing a cursor.

--@browse_information_mode
-- analyzed with no additional information
exec sp_describe_first_result_set N'SELECT * FROM MyTable WHERE Name = @name',
 N'@name varchar(1024)',
 0;

-- analyzed FOR BROWSE
exec sp_describe_first_result_set N'SELECT * FROM MyTable WHERE Name = @name',
 N'@name varchar(1024)',
 1;

-- analyzed as if it was preparing a cursor
exec sp_describe_first_result_set N'SELECT * FROM MyTable WHERE Name = @name',
 N'@name varchar(1024)',
 2;

When the three calls to the stored procedure are run we see three result sets, that are very similar:

However the third result set analyzing it as a cursor shows an extra column called ROWSTAT that is used internally as part of a cursor.  Where we see the real differences are if we scroll the results to the right as shown below:

Here you can see in the red rectangle that the 2nd and third calls to the sp_describe_first_result_set stored procedure return more details about the database, schema, table, and column names than the first call did.

Views

The sp_describe_first_result_set stored procedure can be used to examine the result from a query that is selecting from a view in a simliar fashion to what we covered here selecting from a table.

Summary

The sp_describe_first_result_set stored procedure can be called with a single parameter of just a query. In this case it defaults to no parameters, and to no additional information. The sp_describe_first_result_set stored procedure can also be called with two parameters, where the second parameter is the parameterized query information, which will return the similar results to the first call.

An optional third parameter called browse_information_mode can be used to to get additional details in the result set.

Just another new feature in SQL Server 2012.