Retrieve column definition for stored procedure result set

I'm working with stored procedures in SQL Server 2008 and I've come to learn that I have to INSERT INTO a temp table that has been predefined in order to work with the data. That's fine, except how do I figure out how to define my temp table, if I'm not the one that wrote the stored procedure other than listing its definition and reading through the code?

For example, what would my temporary table look like for `EXEC sp_stored_procedure'? That is a simple stored procedure, and I could probably guess at the data types, but it seems there must be a way to just read the type and length of the columns returned from executing the procedure.

Solution 1:

So let's say you have a stored procedure in tempdb:

USE tempdb;
GO

CREATE PROCEDURE dbo.my_procedure
AS
BEGIN
    SET NOCOUNT ON;

    SELECT foo = 1, bar = 'tooth';
END
GO

There is a quite convoluted way you can go about determining the metadata that the stored procedure will output. There are several caveats, including the procedure can only output a single result set, and that a best guess will be made about the data type if it can't be determined precisely. It requires the use of OPENQUERY and a loopback linked server with the 'DATA ACCESS' property set to true. You can check sys.servers to see if you already have a valid server, but let's just create one manually called loopback:

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

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

Now that you can query this as a linked server, you can use the result of any query (including a stored procedure call) as a regular SELECT. So you can do this (note that the database prefix is important, otherwise you will get error 11529 and 2812):

SELECT * FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;');

If we can perform a SELECT *, we can also perform a SELECT * INTO:

SELECT * INTO #tmp FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;');

And once that #tmp table exists, we can determine the metadata by saying (assuming SQL Server 2005 or greater):

SELECT c.name, [type] = t.name, c.max_length, c.[precision], c.scale
  FROM sys.columns AS c
  INNER JOIN 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');

(If you're using SQL Server 2000, you can do something similar with syscolumns, but I don't have a 2000 instance handy to validate an equivalent query.)

Results:

name      type    max_length precision scale
--------- ------- ---------- --------- -----
foo       int              4        10     0
bar       varchar          5         0     0

In Denali, this will be much, much, much easier. Again there is still a limitation of the first result set but you don't have to set up a linked server and jump through all those hoops. You can just say:

DECLARE @sql NVARCHAR(MAX) = N'EXEC tempdb.dbo.my_procedure;';

SELECT name, system_type_name
    FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);

Results:

name      system_type_name
--------- ----------------
foo       int             
bar       varchar(5)      

Until Denali, I suggest it would be easier to just roll up your sleeves and figure out the data types on your own. Not just because it's tedious to go through the above steps, but also because you are far more likely to make a correct (or at least more accurate) guess than the engine will, since the data type guesses the engine makes will be based on runtime output, without any external knowledge of the domain of possible values. This factor will remain true in Denali as well, so don't get the impression that the new metadata discovery features are a be-all end-all, they just make the above a bit less tedious.

Oh and for some other potential gotchas with OPENQUERY, see Erland Sommarskog's article here:

http://www.sommarskog.se/share_data.html#OPENQUERY

Solution 2:

It looks like in SQL 2012 there is a new SP to help with this.

exec sp_describe_first_result_set N'PROC_NAME'

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql

Solution 3:

A less sophisticated way (that could be sufficient in some cases): edit your original SP, after the final SELECT and before the FROM clause add INSERT INTO tmpTable to save the SP result in tmpTable.

Run the modified SP, preferably with meaningful parameters in order to get actual data. Restore the original code of the procedure.

Now you can get the script of tmpTable from SQL server management studio or query sys.columns to get fields descriptions.

Solution 4:

Here is some code that I wrote. The idea is (as someone else stated) is to get the SP code, modify it and execute it. However, my code does not change the original SP.

First step, get the definition of the SP, strip the 'Create' part out and get rid of the 'AS' after the declaration of parameters, if exists.

Declare @SPName varchar(250)
Set nocount on

Declare @SQL Varchar(max), @SQLReverse Varchar(MAX), @StartPos int, @LastParameterName varchar(250) = '', @TableName varchar(36) = 'A' + REPLACE(CONVERT(varchar(36), NewID()), '-', '')

Select * INTO #Temp from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME = 'ADMIN_Sync_CompareDataForSync'

if @@ROWCOUNT > 0
    BEGIN
        Select @SQL = REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']', 'Declare') 
        from INFORMATION_SCHEMA.ROUTINES 
        where ROUTINE_NAME = @SPName

        Select @LastParameterName = PARAMETER_NAME + ' ' + DATA_TYPE + 
            CASE WHEN CHARACTER_MAXIMUM_LENGTH is not null THEN '(' + 
                CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH) END + ')' ELSE '' END 
        from #Temp 
        WHERE ORDINAL_POSITION = 
            (Select MAX(ORDINAL_POSITION) 
            From #Temp)

        Select @StartPos = CHARINDEX(@LastParameterName, REPLACE(@SQL, '  ', ' '), 1) + LEN(@LastParameterName)
    END
else
    Select @SQL = REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']', '') from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = @SPName

DROP TABLE #Temp

Select @StartPos = CHARINDEX('AS', UPPER(@SQL), @StartPos)

Select @SQL = STUFF(@SQL, @StartPos, 2, '')

(Note the creation of a new table name based on a unique identifier) Now find the last 'From' word in the code assuming this is the code that does the select that returns the result set.

Select @SQLReverse = REVERSE(@SQL)

Select @StartPos = CHARINDEX('MORF', UPPER(@SQLReverse), 1)

Change the code to select the resultset into a table (the table based on the uniqueidentifier)

Select @StartPos = LEN(@SQL) - @StartPos - 2

Select @SQL = STUFF(@SQL, @StartPos, 5, ' INTO ' + @TableName + ' FROM ')

EXEC (@SQL)

The result set is now in a table, it does not matter if the table is empty!

Lets get the structure of the table

Select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

You can now do your magic with this

Don't forget to drop that unique table

Select @SQL = 'drop table ' + @TableName

Exec (@SQL)

Hope this helps!