Jasinski Technical Wiki

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

PoweredBy

Page History: Column Definitions for Stored Procedure Result Set

Compare Page Revisions



« 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


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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.