Page History: Column Definitions for Stored Procedure Result Set

Compare Page Revisions

2 1 0 Current 2 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Fri, Oct 30, 2015, 9:16 AM

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')