Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Column Definitions for Stored Procedure Result Set

Overview¶

Starting in SQL Server 2012, Microsoft added the sp_describe_first_result_set system stored procedure to return the metadata of the first result set from a stored procedure. This article describes how to retrieve that information from SQL Server 2008.

Reference¶

• sp_describe_first_result_set - https://technet.microsoft.com/en-us/library/ff878602.aspx
• http://stackoverflow.com/questions/7368864/retrieve-column-definition-for-stored-procedure-result-set
  

Procedure¶

1. Create loopback linked server.

IMPORTANT NOTE: If @@SERVERNAME doesn't properly return the name of your instance, then replace it in the following script with the correct string literal.

EXEC master..sp_addlinkedserver 
    @server = 'loopback',  
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = @@SERVERNAME;

EXEC master..sp_serveroption 
    @server = 'loopback', 
    @optname = 'DATA ACCESS',
    @optvalue = 'TRUE';

2. For each stored procedure of interest, execute the following script, replacing the stored procedure call with a call to the stored procedure of interest.

if object_id('tempdb..#tmp') is not null drop table #tmp
go
/*  TODO: The following line is only an example.  Replace 'exec [MyDatabase].[MySchema].[sp_MyStoredProc] 0,0,null'
    with your own stored procedure call, including parameter values
*/
select top 10 * into #tmp from openquery(loopback, 'exec [MyDatabase].[MySchema].[sp_MyStoredProc] 0,0,null')

SELECT 
    c.name
    ,[type] = t.name
    ,c.max_length
    ,c.[precision]
    ,c.scale
    ,c.is_nullable

FROM 
    tempdb.sys.columns AS c

    inner JOIN tempdb.sys.types AS t
        ON c.system_type_id = t.system_type_id
        AND c.user_type_id = t.user_type_id

WHERE 
    c.[object_id] = object_id('tempdb..#tmp')

Reusable Code¶

create procedure dbo.GetStoredProcedureOutputStructure
    @spName nvarchar(300)
as
/*--- Begin Testing ---*/
/*
declare 
     @spName    nvarchar(300)   = '[MySchema].[sp_MyStoredProc]'
*/
/*--- End Testing ---*/

if object_id(@spName) is not null begin

    /*--- Calculate Param List as All Nulls (separated by commas) ---*/
    declare @xml xml = (
        select 
            [@v] = 'null'
        from 
            sys.procedures pr 
            inner join sys.parameters p 
                on pr.object_id = p.object_id
            inner join sys.schemas s
                on pr.schema_id = s.schema_id
        where 1=1
            and '[' + s.name + '].[' + pr.name +']' = @spName
        for xml path ('x')
        )

    declare @spParams nvarchar(4000) = convert(nvarchar(4000), @xml)

    set @spParams = replace(@spParams, '"/><x v="', ', ')
    set @spParams = replace(@spParams, '<x v="', '')
    set @spParams = replace(@spParams, '"/>', '')


    /*--- Calculate OpenQuery statement and call it ---*/
    if object_id(N'tempdb..##tmp') is not null drop table ##tmp

    set @spName = '[' + db_name() + '].' + @spName

    declare @openquery nvarchar(4000) = 'select top 10 * into ##tmp from openquery(loopback, ''exec ' + @spName + ' ' + @spParams + ''')'
     
    exec (@openquery)

    /*--- Return Output Structure as XML ---*/
    SELECT 
         [@Name] = c.name
        ,[@DataType] = t.name
        ,[@MaxLength] = c.max_length
        ,[@Precision] = c.[precision]
        ,[@Scale] = c.scale
        ,[@IsNullable] = c.is_nullable

    FROM 
        tempdb.sys.columns AS c

        inner JOIN tempdb.sys.types AS t
            ON c.system_type_id = t.system_type_id
            AND c.user_type_id = t.user_type_id

    WHERE 
        c.[object_id] = object_id('tempdb..##tmp')

    for xml path ('ColumnDef')

end