In SQL Server, the sp_describe_first_result_set system stored procedure returns the metadata for a result set.
More specifically, it returns the metadata for the first possible result set of a T-SQL batch.
It accepts three parameters, the first of which is the T-SQL statement/s you’re analysing.
Example
Here’s an example to demonstrate.
EXEC sp_describe_first_result_set
@tsql = N'SELECT * FROM Artists',
@params = null,
@browse_information_mode = 0;That code explicitly names each of the three parameters that this stored procedure accepts.
Only the first parameter is required. Also, as with any stored procedure, you can omit the parameter names if you prefer.
So we could also use the following code:
EXEC sp_describe_first_result_set N'SELECT * FROM Artists';And here’s the result:
+-------------+------------------+------------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+---------------+--------------+--------------------+-------------------------+ | is_hidden | column_ordinal | name | is_nullable | system_type_id | system_type_name | max_length | precision | scale | collation_name | user_type_id | user_type_database | user_type_schema | user_type_name | assembly_qualified_type_name | xml_collection_id | xml_collection_database | xml_collection_schema | xml_collection_name | is_xml_document | is_case_sensitive | is_fixed_length_clr_type | source_server | source_database | source_schema | source_table | source_column | is_identity_column | is_part_of_unique_key | is_updateable | is_computed_column | is_sparse_column_set | ordinal_in_order_by_list | order_by_is_descending | order_by_list_length | tds_type_id | tds_length | tds_collation_id | tds_collation_sort_id | |-------------+------------------+------------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+---------------+--------------+--------------------+-------------------------| | 0 | 1 | ArtistId | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 1 | NULL | 0 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL | | 0 | 2 | ArtistName | 0 | 231 | nvarchar(255) | 510 | 0 | 0 | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 0 | NULL | 1 | 0 | 0 | NULL | NULL | NULL | 231 | 510 | 13632521 | 52 | | 0 | 3 | ActiveFrom | 1 | 40 | date | 3 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 0 | NULL | 1 | 0 | 0 | NULL | NULL | NULL | 40 | 3 | NULL | NULL | +-------------+------------------+------------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+---------------+--------------+--------------------+-------------------------+
There are a lot of columns returned with this stored procedure (see Microsoft’s docs for an explanation of each), so you’ll probably need to scroll sideways to see all of them.
I’ll switch to vertical output in some of my next examples, in case you have problems scrolling sideways.
Browse Mode
The third argument – @browse_information_mode – specifies if additional key columns and source table information are returned.
The following values are accepted for this argument:
| Value | Result |
|---|---|
0 | No information is returned. |
1 | Each query is analysed as if it includes a FOR BROWSE option on the query. This will return base table names as the source column information. |
2 | Each query is analysed as if it would be used in preparing or executing a cursor. This will return view names as source column information. |
Below are examples that illustrate how each of these values affect the result.
To make things more concise, I’ll modify my T-SQL statement to return only one column. I’ll also display the results using vertical output to save you from having to scroll sideways.
@browse_information_mode = 0
In this example I set @browse_information_mode to 0.
EXEC sp_describe_first_result_set
@tsql = N'SELECT AlbumName FROM vAlbums',
@params = null,
@browse_information_mode = 0;As previously mentioned, you can alternatively omit the parameter names, if you prefer. Therefore, we could use the following code as a more concise way of doing the same thing.
EXEC sp_describe_first_result_set N'SELECT AlbumName FROM vAlbums', null, 0;Result (using vertical output):
is_hidden | 0 column_ordinal | 1 name | AlbumName is_nullable | 0 system_type_id | 231 system_type_name | nvarchar(255) max_length | 510 precision | 0 scale | 0 collation_name | SQL_Latin1_General_CP1_CI_AS user_type_id | NULL user_type_database | NULL user_type_schema | NULL user_type_name | NULL assembly_qualified_type_name | NULL xml_collection_id | NULL xml_collection_database | NULL xml_collection_schema | NULL xml_collection_name | NULL is_xml_document | 0 is_case_sensitive | 0 is_fixed_length_clr_type | 0 source_server | NULL source_database | NULL source_schema | NULL source_table | NULL source_column | NULL is_identity_column | 0 is_part_of_unique_key | NULL is_updateable | 1 is_computed_column | 0 is_sparse_column_set | 0 ordinal_in_order_by_list | NULL order_by_is_descending | NULL order_by_list_length | NULL tds_type_id | 231 tds_length | 510 tds_collation_id | 13632521 tds_collation_sort_id | 52
Notice that quite a few columns are NULL. In particular, notice that the source_database, source_schema, source_table, and source_column columns are NULL.
These columns won’t be NULL in the next two examples, yet they will produce two different results.
@browse_information_mode = 1
In this example I set @browse_information_mode to 1.
EXEC sp_describe_first_result_set
N'SELECT AlbumName FROM vAlbums', null, 1;Setting @browse_information_mode to 1 returns the result as if it includes a FOR BROWSE option on the query.
In our case, this results in four rows being returned (representing four columns from the base tables).
Here are the four rows returned:
+-------------+------------------+-----------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+---------------+--------------+--------------------+-------------------------+ | is_hidden | column_ordinal | name | is_nullable | system_type_id | system_type_name | max_length | precision | scale | collation_name | user_type_id | user_type_database | user_type_schema | user_type_name | assembly_qualified_type_name | xml_collection_id | xml_collection_database | xml_collection_schema | xml_collection_name | is_xml_document | is_case_sensitive | is_fixed_length_clr_type | source_server | source_database | source_schema | source_table | source_column | is_identity_column | is_part_of_unique_key | is_updateable | is_computed_column | is_sparse_column_set | ordinal_in_order_by_list | order_by_is_descending | order_by_list_length | tds_type_id | tds_length | tds_collation_id | tds_collation_sort_id | |-------------+------------------+-----------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+---------------+--------------+--------------------+-------------------------| | 0 | 1 | AlbumName | 0 | 231 | nvarchar(255) | 510 | 0 | 0 | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | Music | dbo | Albums | AlbumName | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | 231 | 510 | 13632521 | 52 | | 1 | 2 | ArtistId | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | Music | dbo | Artists | ArtistId | 1 | 1 | 0 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL | | 1 | 3 | AlbumId | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | Music | dbo | Albums | AlbumId | 1 | 1 | 0 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL | | 1 | 4 | GenreId | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | Music | dbo | Genres | GenreId | 1 | 1 | 0 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL | +-------------+------------------+-----------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+---------------+--------------+--------------------+-------------------------+
So even though I only specified just one column in the T-SQL query that I passed to the procedure, it returned info for four columns. These are the four columns that are referenced by the vAlbums view.
Let’s hone in on just one column using vertical output:
is_hidden | 0 column_ordinal | 1 name | AlbumName is_nullable | 0 system_type_id | 231 system_type_name | nvarchar(255) max_length | 510 precision | 0 scale | 0 collation_name | SQL_Latin1_General_CP1_CI_AS user_type_id | NULL user_type_database | NULL user_type_schema | NULL user_type_name | NULL assembly_qualified_type_name | NULL xml_collection_id | NULL xml_collection_database | NULL xml_collection_schema | NULL xml_collection_name | NULL is_xml_document | 0 is_case_sensitive | 0 is_fixed_length_clr_type | 0 source_server | NULL source_database | Music source_schema | dbo source_table | Albums source_column | AlbumName is_identity_column | 0 is_part_of_unique_key | 0 is_updateable | 1 is_computed_column | 0 is_sparse_column_set | 0 ordinal_in_order_by_list | NULL order_by_is_descending | NULL order_by_list_length | NULL tds_type_id | 231 tds_length | 510 tds_collation_id | 13632521 tds_collation_sort_id | 52
Notice that the source_database, source_schema, source_table, and source_column columns are no longer NULL. They provide information about the base objects queried by the view.
In contrast, when we set @browse_information_mode to 2 in the next example, we’ll get the actual columns in the view.
Also notice that the source_server column is still NULL. This is because the local server is the originating server. But if my view was querying tables on a linked server, the name of that server would be in the source_server column.
See How sys.dm_exec_describe_first_result_set Works for an example. In that article I use a similar view to the one in the above example, except that it queries a database on a linked server.
@browse_information_mode = 2
Finally, let’s set @browse_information_mode to 2.
EXEC sp_describe_first_result_set
N'SELECT AlbumName FROM vAlbums', null, 2;In this case, the query is analysed as if it would be used in preparing or executing a cursor.
This time, only two rows are returned:
+-------------+------------------+-----------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+---------------+--------------+--------------------+-------------------------+ | is_hidden | column_ordinal | name | is_nullable | system_type_id | system_type_name | max_length | precision | scale | collation_name | user_type_id | user_type_database | user_type_schema | user_type_name | assembly_qualified_type_name | xml_collection_id | xml_collection_database | xml_collection_schema | xml_collection_name | is_xml_document | is_case_sensitive | is_fixed_length_clr_type | source_server | source_database | source_schema | source_table | source_column | is_identity_column | is_part_of_unique_key | is_updateable | is_computed_column | is_sparse_column_set | ordinal_in_order_by_list | order_by_is_descending | order_by_list_length | tds_type_id | tds_length | tds_collation_id | tds_collation_sort_id | |-------------+------------------+-----------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+---------------+--------------+--------------------+-------------------------| | 0 | 1 | AlbumName | 0 | 231 | nvarchar(255) | 510 | 0 | 0 | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | Music | dbo | vAlbums | AlbumName | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | 231 | 510 | 13632521 | 52 | | 1 | 2 | ROWSTAT^@ | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL | +-------------+------------------+-----------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+---------------+--------------+--------------------+-------------------------+
And to save you from scrolling sideways, here’s the first row in vertical output:
is_hidden | 0 column_ordinal | 1 name | AlbumName is_nullable | 0 system_type_id | 231 system_type_name | nvarchar(255) max_length | 510 precision | 0 scale | 0 collation_name | SQL_Latin1_General_CP1_CI_AS user_type_id | NULL user_type_database | NULL user_type_schema | NULL user_type_name | NULL assembly_qualified_type_name | NULL xml_collection_id | NULL xml_collection_database | NULL xml_collection_schema | NULL xml_collection_name | NULL is_xml_document | 0 is_case_sensitive | 0 is_fixed_length_clr_type | 0 source_server | NULL source_database | Music source_schema | dbo source_table | vAlbums source_column | AlbumName is_identity_column | 0 is_part_of_unique_key | 0 is_updateable | 1 is_computed_column | 0 is_sparse_column_set | 0 ordinal_in_order_by_list | NULL order_by_is_descending | NULL order_by_list_length | NULL tds_type_id | 231 tds_length | 510 tds_collation_id | 13632521 tds_collation_sort_id | 52
In this example, the source_table column contains the actual view name, rather than the base table.
The @params Argument
So far, we haven’t used the @params argument, other than to set it to NULL.
If the SQL batch that you’re analysing contains parameters, use the @params function to declare those parameters.
This works similar to the way you declare parameters when using the sp_executesql procedure.
Here’s an example that declares a parameter with the @params argument.
EXEC sp_describe_first_result_set
@tsql = N'SELECT AlbumName FROM Albums WHERE ArtistId = @ArtistId',
@params = N'@ArtistId int',
@browse_information_mode = 0;Result (using vertical output):
is_hidden | 0 column_ordinal | 1 name | AlbumName is_nullable | 0 system_type_id | 231 system_type_name | nvarchar(255) max_length | 510 precision | 0 scale | 0 collation_name | SQL_Latin1_General_CP1_CI_AS user_type_id | NULL user_type_database | NULL user_type_schema | NULL user_type_name | NULL assembly_qualified_type_name | NULL xml_collection_id | NULL xml_collection_database | NULL xml_collection_schema | NULL xml_collection_name | NULL is_xml_document | 0 is_case_sensitive | 0 is_fixed_length_clr_type | 0 source_server | NULL source_database | NULL source_schema | NULL source_table | NULL source_column | NULL is_identity_column | 0 is_part_of_unique_key | NULL is_updateable | 1 is_computed_column | 0 is_sparse_column_set | 0 ordinal_in_order_by_list | NULL order_by_is_descending | NULL order_by_list_length | NULL tds_type_id | 231 tds_length | 510 tds_collation_id | 13632521 tds_collation_sort_id | 52