If you have a stored procedure which returns two result sets up till now there was now way to get the meta data easily about the first result set. Let’s take a look what is new in SQL Server Denali, first create this very simple stored procedure.

T-SQL
1
2
3
4
5
6

create procedure prTest
as
select 1 as a, 'B' as b
 
select 'A' as z, 3 as d
Go
create procedure prTest
as
select 1 as a, 'B' as b

select 'A' as z, 3 as d
Go

sys.dm_exec_describe_first_result_set_for_object

Run the following query

T-SQL
1
2

select * 
from sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('prTest'),1)
select * 
from sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('prTest'),1)

Here is partial output

is_hidden column_ordinal name is_nullable system_type_id system_type_name max_length precision scale
0 1 a 0 56 int 4 10 0
0 2 b 0 167 varchar(1) 1 0 0

As you can see you get the name, type, precision, length and more about the resultset

sys.dm_exec_describe_first_result_set

This query is more interesting because it will look at a dynamic query. Let’s say your query is the following ‘SELECT * FROM sysobjects SELECT 3’ passed in as a parameter

Here is how that would work

T-SQL
1
2
3
4

declare @n nvarchar(100) = N'SELECT * FROM sysobjects SELECT 3'
 
SELECT *
FROM sys.dm_exec_describe_first_result_set(@n, NULL, 1);
declare @n nvarchar(100) = N'SELECT * FROM sysobjects SELECT 3'

SELECT *
FROM sys.dm_exec_describe_first_result_set(@n, NULL, 1);

Here is partial output

is_hidden column_ordinal name is_nullable system
_type_id		 system_type
_name		 max_length precision scale
0 1 name 0 231 nvarchar(128) 256 0 0
0 2 id 0 56 int 4 10 0
0 3 xtype 0 175 char(2) 2 0 0
0 4 uid 1 52 smallint 2 5 0
0 5 info 1 52 smallint 2 5 0
0 6 status 1 56 int 4 10 0
0 7 base_schema_ver 1 56 int 4 10 0
0 8 replinfo 1 56 int 4 10 0
0 9 parent_obj 0 56 int 4 10 0
0 10 crdate 0 61 datetime 8 23 3
0 11 ftcatid 1 52 smallint 2 5 0
0 12 schema_ver 1 56 int 4 10 0
0 13 stats_schema_ver 1 56 int 4 10 0
0 14 type 1 175 char(2) 2 0 0
0 15 userstat 1 52 smallint 2 5 0
0 16 sysstat 1 52 smallint 2 5 0
0 17 indexdel 1 52 smallint 2 5 0
0 18 refdate 0 61 datetime 8 23 3
0 19 version 1 56 int 4 10 0
0 20 deltrig 1 56 int 4 10 0
0 21 instrig 1 56 int 4 10 0

As you can see you get the name, type, precision, length and more about the resultset, this is pretty neat especially for a query stored in a parameter.

When I wrote this post documentation was not yet available, so some of this stuff is written with a lot of guessing.

Click on the SQL Server Denali tag to see all our SQL Server Denali related posts

Related Posts