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.

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

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

Here is partial output

  <th>
    column_ordinal
  </th>
  
  <th>
    name
  </th>
  
  <th>
    is_nullable
  </th>
  
  <th>
    system_type_id
  </th>
  
  <th>
    system_type_name
  </th>
  
  <th>
    max_length
  </th>
  
  <th>
    precision
  </th>
  
  <th>
    scale
  </th>
</tr>

<tr>
  <td>
  </td>
  
  <td>
    1
  </td>
  
  <td>
    a
  </td>
  
  <td>
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
  </td>
</tr>

<tr>
  <td>
  </td>
  
  <td>
    2
  </td>
  
  <td>
    b
  </td>
  
  <td>
  </td>
  
  <td>
    167
  </td>
  
  <td>
    varchar(1)
  </td>
  
  <td>
    1
  </td>
  
  <td>
  </td>
  
  <td>
  </td>
</tr>
is_hidden

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

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

  <th>
    column_ordinal
  </th>
  
  <th>
    name
  </th>
  
  <th>
    is_nullable
  </th>
  
  <th>
    system<br />_type_id
  </th>
  
  <th>
    system_type<br />_name
  </th>
  
  <th>
    max_length
  </th>
  
  <th>
    precision
  </th>
  
  <th>
    scale
  </th>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    1
  </td>
  
  <td>
    name
  </td>
  
  <td>
    0
  </td>
  
  <td>
    231
  </td>
  
  <td>
    nvarchar(128)
  </td>
  
  <td>
    256
  </td>
  
  <td>
    0
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    2
  </td>
  
  <td>
    id
  </td>
  
  <td>
    0
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    3
  </td>
  
  <td>
    xtype
  </td>
  
  <td>
    0
  </td>
  
  <td>
    175
  </td>
  
  <td>
    char(2)
  </td>
  
  <td>
    2
  </td>
  
  <td>
    0
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    4
  </td>
  
  <td>
    uid
  </td>
  
  <td>
    1
  </td>
  
  <td>
    52
  </td>
  
  <td>
    smallint
  </td>
  
  <td>
    2
  </td>
  
  <td>
    5
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    5
  </td>
  
  <td>
    info
  </td>
  
  <td>
    1
  </td>
  
  <td>
    52
  </td>
  
  <td>
    smallint
  </td>
  
  <td>
    2
  </td>
  
  <td>
    5
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    6
  </td>
  
  <td>
    status
  </td>
  
  <td>
    1
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    7
  </td>
  
  <td>
    base_schema_ver
  </td>
  
  <td>
    1
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    8
  </td>
  
  <td>
    replinfo
  </td>
  
  <td>
    1
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    9
  </td>
  
  <td>
    parent_obj
  </td>
  
  <td>
    0
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    10
  </td>
  
  <td>
    crdate
  </td>
  
  <td>
    0
  </td>
  
  <td>
    61
  </td>
  
  <td>
    datetime
  </td>
  
  <td>
    8
  </td>
  
  <td>
    23
  </td>
  
  <td>
    3
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    11
  </td>
  
  <td>
    ftcatid
  </td>
  
  <td>
    1
  </td>
  
  <td>
    52
  </td>
  
  <td>
    smallint
  </td>
  
  <td>
    2
  </td>
  
  <td>
    5
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    12
  </td>
  
  <td>
    schema_ver
  </td>
  
  <td>
    1
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    13
  </td>
  
  <td>
    stats_schema_ver
  </td>
  
  <td>
    1
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    14
  </td>
  
  <td>
    type
  </td>
  
  <td>
    1
  </td>
  
  <td>
    175
  </td>
  
  <td>
    char(2)
  </td>
  
  <td>
    2
  </td>
  
  <td>
    0
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    15
  </td>
  
  <td>
    userstat
  </td>
  
  <td>
    1
  </td>
  
  <td>
    52
  </td>
  
  <td>
    smallint
  </td>
  
  <td>
    2
  </td>
  
  <td>
    5
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    16
  </td>
  
  <td>
    sysstat
  </td>
  
  <td>
    1
  </td>
  
  <td>
    52
  </td>
  
  <td>
    smallint
  </td>
  
  <td>
    2
  </td>
  
  <td>
    5
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    17
  </td>
  
  <td>
    indexdel
  </td>
  
  <td>
    1
  </td>
  
  <td>
    52
  </td>
  
  <td>
    smallint
  </td>
  
  <td>
    2
  </td>
  
  <td>
    5
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    18
  </td>
  
  <td>
    refdate
  </td>
  
  <td>
    0
  </td>
  
  <td>
    61
  </td>
  
  <td>
    datetime
  </td>
  
  <td>
    8
  </td>
  
  <td>
    23
  </td>
  
  <td>
    3
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    19
  </td>
  
  <td>
    version
  </td>
  
  <td>
    1
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    20
  </td>
  
  <td>
    deltrig
  </td>
  
  <td>
    1
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
    0
  </td>
</tr>

<tr>
  <td>
    0
  </td>
  
  <td>
    21
  </td>
  
  <td>
    instrig
  </td>
  
  <td>
    1
  </td>
  
  <td>
    56
  </td>
  
  <td>
    int
  </td>
  
  <td>
    4
  </td>
  
  <td>
    10
  </td>
  
  <td>
    0
  </td>
</tr>
is_hidden

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