Blog

The system stored procedure sp_describe_first_result_set was introduced in SQL Server 2012 and it returns meta data about the first submitted T-SQL statement.

The procedure accepts three parameters:

@tsql – A string (must be nvarchar) that contains the T-SQL statement to return meta data about.

@params – Optional string of parameters used in the @tsql statement. Default is NULL.

@browse_information_mode – This parameter controls whether meta data about the source of columns is returned. More specifically, if information on database, schema, table and primary keys are included in the result. 0 (default) means no such meta data is returned. 1 means meta data about base tables are returned, and 2 means meta data about view names (if any) are returned.

Let’s create a simple example with a table and a view:

CREATE TABLE T1
	(
		COLPK INT IDENTITY(1,1) NOT NULL CONSTRAINT T1_PK PRIMARY KEY CLUSTERED
		,COLTEXT VARCHAR(MAX) NOT NULL
	)
GO

CREATE VIEW V1 AS
SELECT COLPK, COLTEXT FROM T1
GO

In the first example we will use defaults:

SP_DESCRIBE_FIRST_RESULT_SET @tsql = N'SELECT COLTEXT AS NEWCOL FROM V1'

A lot of columns are returned, a complete reference can be found on TechNet.

Below I have included some of the columns that are returned:

If we want information on the base tables that are used in the view in our statement, we set the parameter @browse_information_mode to 1:

SP_DESCRIBE_FIRST_RESULT_SET @tsql = N'SELECT COLTEXT AS NEWCOL FROM V1', @params = NULL, @browse_information_mode = 1

Now more information is returned:

Information on the base table is included, as well as information on the key. Note that the column is_hidden is 1 on the key.

In the last example we want information on the view instead of the base table, so we set parameter @browse_information_mode to 2:

SP_DESCRIBE_FIRST_RESULT_SET @tsql = N'SELECT COLTEXT AS NEWCOL FROM V1', @params = NULL, @browse_information_mode = 2

This time information on the view is returned instead. There is an extra hidden row this time too. This time it is meant to use “in a cursor”. In the previous example the key was returned to use in “browsing” the result.

There is also a dynamic management view that returns the same information: sys.dm_exec_describe_first_result_set.

It is important to note that some valid T-SQL will generate an error if submitted to this system stored procedure. A simple example is if the number of columns can differ:

SP_DESCRIBE_FIRST_RESULT_SET @tsql = N'
IF(1=1)
    SELECT COLPK FROM T1;
ELSE
    SELECT COLPK, COLTEXT FROM T1'
GO

This statement will result in an error:

Msg 11509, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 The metadata could not be determined because the statement ‘SELECT COLPK FROM T1;’ is not compatible with the statement ‘SELECT COLPK, COLTEXT FROM T1’.

This TechNet article contains more information on the types of problems that can occur when using this procedure: http://technet.microsoft.com/en-us/library/ff878602.aspx.

Another useful system stored procedure added in SQL Server 2012 is sys.dm_exec_describe_first_result_set_for_object that returns meta data about any object in the database.