If you ever find yourself in the situation where you’re about to execute a stored procedure, but you suddenly catch yourself. You wonder “How many columns does this thing return? Which tables? Does it query a remote server?”
The good news is there are several ways to get this information before you run the query. Let’s look at them.
The sys.dm_exec_describe_first_result_set_for_object Function
First stop is the sys.dm_exec_describe_first_result_set_for_object system dynamic management function.
This function takes an @object_id as a parameter and describes the first result metadata for the module with that ID.
It can be used on stored procedures and triggers.
Here’s an example to demonstrate. In this case, I use the OBJECT_ID() function to return the ID of the stored procedure (which saves me from having to know the actual ID).
SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object(
OBJECT_ID('spAlbumsFromArtist'),
0
); 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 | error_number | error_severity | error_state | error_message | error_type | error_type_desc | |-------------+------------------+-------------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+----------------+------------------+---------------+-----------------+--------------+-------------------| | 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 | NULL | NULL | NULL | NULL | 0 | NULL | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 0 | 2 | ReleaseDate | 0 | 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 | NULL | NULL | NULL | NULL | NULL | NULL | +-------------+------------------+-------------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+----------------+------------------+---------------+-----------------+--------------+-------------------+
If you scroll far enough sideways, you’ll see that the source_server, source_database, source_schema, source_table, and source_column columns are NULL. This is because we used 0 as the third argument.
Switching the third argument to 1 will return information about the base tables referenced in the stored procedure.
SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object(
OBJECT_ID('spAlbumsFromArtist'),
1
); 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 | error_number | error_severity | error_state | error_message | error_type | error_type_desc | |-------------+------------------+-------------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+----------------+------------------+---------------+-----------------+--------------+-------------------| | 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 | Homer | Music | dbo | Albums | AlbumName | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 0 | 2 | ReleaseDate | 0 | 40 | date | 3 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | Homer | Music | dbo | Albums | ReleaseDate | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | 3 | AlbumId | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | Homer | Music | dbo | Albums | AlbumId | 0 | 1 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | 4 | ArtistId | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | Homer | Music | dbo | Artists | ArtistId | 0 | 1 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-------------+------------------+-------------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+----------------+------------------+---------------+-----------------+--------------+-------------------+
Now when we scroll sideways, we can see that the source_server, source_database, source_schema, source_table, and source_column columns all contain values.
In this case, the procedure queries tables across a linked server called “Homer”.
You’ll also notice that there are now four rows returned, instead of just two that were returned when we set the third argument to 0. The two extra rows reflect the underlying columns that are queried within the stored procedure.
Here’s the stored procedure’s definition:
CREATE PROCEDURE [dbo].[spAlbumsFromArtist]
@ArtistName varchar(255)
AS
SELECT AlbumName, ReleaseDate
FROM Homer.Music.dbo.Albums al
INNER JOIN Homer.Music.dbo.Artists ar
ON al.ArtistId = ar.ArtistId
WHERE ar.ArtistName = @ArtistName;So, even though the procedure returns just two columns, it needs to reference other columns in order to complete its task.
See How sys.dm_exec_describe_first_result_set_for_object Works for more examples.
The sys.dm_exec_describe_first_result_set View
This view uses the same algorithm as the previous view, and returns the same information. The difference is that it analyses a T-SQL batch, rather than the actual stored procedure.
This means you can use this view to analyse code that isn’t necessarily in a stored procedure or trigger. For example, you can use it to analyse a view or an ad hoc query.
But this article is about analysing stored procedures, so here’s an example of doing just that.
SELECT *
FROM sys.dm_exec_describe_first_result_set(
'EXEC spAlbumsFromArtist @ArtistId = ''Iron Maiden''',
null,
1
);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 | error_number | error_severity | error_state | error_message | error_type | error_type_desc | |-------------+------------------+-------------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+----------------+------------------+---------------+-----------------+--------------+-------------------| | 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 | Homer | Music | dbo | Albums | AlbumName | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 0 | 2 | ReleaseDate | 0 | 40 | date | 3 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | Homer | Music | dbo | Albums | ReleaseDate | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | 3 | AlbumId | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | Homer | Music | dbo | Albums | AlbumId | 0 | 1 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | 4 | ArtistId | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | Homer | Music | dbo | Artists | ArtistId | 0 | 1 | 1 | 0 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-------------+------------------+-------------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+----------------+------------------+---------------+-----------------+--------------+-------------------+
See How sys.dm_exec_describe_first_result_set Works for more examples.
The sp_describe_first_result_set System Stored Procedure
The sp_describe_first_result_set system stored procedure does the same thing (and uses the same algorithm). It accepts a T-SQL batch and returns the metadata for its first possible result set.
EXEC sp_describe_first_result_set
@tsql = N'EXEC spAlbumsFromArtist @ArtistId = ''Iron Maiden''',
@params = null,
@browse_information_mode = 1;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 | 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 | Homer | Music | dbo | Albums | AlbumName | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | 231 | 510 | 13632521 | 52 | | 0 | 2 | ReleaseDate | 0 | 40 | date | 3 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | Homer | Music | dbo | Albums | ReleaseDate | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | 40 | 3 | NULL | NULL | | 1 | 3 | AlbumId | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | Homer | Music | dbo | Albums | AlbumId | 0 | 1 | 1 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL | | 1 | 4 | ArtistId | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | Homer | Music | dbo | Artists | ArtistId | 0 | 1 | 1 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL | +-------------+------------------+-------------+---------------+------------------+--------------------+--------------+-------------+---------+------------------------------+----------------+----------------------+--------------------+------------------+--------------------------------+---------------------+---------------------------+-------------------------+-----------------------+-------------------+---------------------+----------------------------+-----------------+-------------------+-----------------+----------------+-----------------+----------------------+-------------------------+-----------------+----------------------+------------------------+----------------------------+--------------------------+------------------------+---------------+--------------+--------------------+-------------------------+
See How sp_describe_first_result_set Works for more examples.
OPENROWSET with a Temporary Table
Another option is to construct a query that puts the top zero results of an OPENROWSET query into a temporary table, then use a procedure like sp_columns to return column information about that temporary table (which contains the data from our result set).
This will therefore provide details, such as column names, data types, etc.
SELECT TOP 0 * INTO #TempTable
FROM OPENROWSET(
'SQLNCLI',
'Server=localhost;Trusted_Connection=yes;',
'EXEC Test.dbo.sp_BadDogs');
EXEC('USE tempdb EXEC sp_columns #TempTable DROP TABLE #TempTable');Result:
+-------------------+---------------+----------------------------------------------------------------------------------------------------------------------------------+---------------+-------------+-------------+-------------+----------+---------+---------+------------+-----------+--------------+-----------------+--------------------+---------------------+--------------------+---------------+----------------+ | TABLE_QUALIFIER | TABLE_OWNER | TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME | PRECISION | LENGTH | SCALE | RADIX | NULLABLE | REMARKS | COLUMN_DEF | SQL_DATA_TYPE | SQL_DATETIME_SUB | CHAR_OCTET_LENGTH | ORDINAL_POSITION | IS_NULLABLE | SS_DATA_TYPE | |-------------------+---------------+----------------------------------------------------------------------------------------------------------------------------------+---------------+-------------+-------------+-------------+----------+---------+---------+------------+-----------+--------------+-----------------+--------------------+---------------------+--------------------+---------------+----------------| | tempdb | dbo | #TempTable__________________________________________________________________________________________________________000000000017 | DogId | 4 | int | 10 | 4 | 0 | 10 | 0 | NULL | NULL | 4 | NULL | NULL | 1 | NO | 56 | | tempdb | dbo | #TempTable__________________________________________________________________________________________________________000000000017 | DogName | -9 | nvarchar | 255 | 510 | NULL | NULL | 1 | NULL | NULL | -9 | NULL | 510 | 2 | YES | 39 | | tempdb | dbo | #TempTable__________________________________________________________________________________________________________000000000017 | GoodDog | -7 | bit | 1 | 1 | NULL | NULL | 1 | NULL | NULL | -7 | NULL | NULL | 3 | YES | 50 | +-------------------+---------------+----------------------------------------------------------------------------------------------------------------------------------+---------------+-------------+-------------+-------------+----------+---------+---------+------------+-----------+--------------+-----------------+--------------------+---------------------+--------------------+---------------+----------------+
Using this method, you could also do things like, swap sp_columns for sp_help or similar.