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.