Retrieve column definition for stored procedure result set

Question

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.

Answer 1

In order to get queryable resultset sys.dm_exec_describe_first_result_set(SQL Server 2012) could be used:

SELECT column_ordinal, name, system_type_name
FROM sys.dm_exec_describe_first_result_set(N'EXEC stored_procedure_name', NULL, 0);

db<>fiddle demo

This soultion has few limitations though for instance SP cannot use temporary tables.

Answer 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://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql

Answer 3

If you are working in an environment with restricted rights where things like loopback linked server seems black magic and are definitely "no way!", but you have a few rights on schema and only a couple of stored procedure to process there is a very simple solution.

You can use the very helpful SELECT INTO syntax, which will create a new table with result set of a query.

Let's say your procedure contains the following Select query :

SELECT x, y, z
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...

Instead replace it by :

SELECT x, y, z
INTO MyOutputTable
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...

When you will execute it, it will create a new table MyOutputTable with the results returned by the query.

You just have to do a right click on its name to get the table definition.

That's all !

SELECT INTO only require the ability to create new tables and also works with temporary tables (SELECT... INTO #MyTempTable), but it could be harder to retrieve the definition.

However of course if you need to retrieve the output definition of a thousands SP, it's not the fastest way :)

Answer 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!

Answer 5

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.

Answer 6

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