Get Stored Procedure Result Column Types

Question

I'm trying to get whatever a stored procedure returns, with the column names and their types. I can do this with tables but I couldn't figure it out for stored procedures. I tried the sp_columns but only managed to make it work for tables.

I have also tried something like this but I'm not sure what I am supposed to match with what.

SELECT * 
FROM SYS.PROCEDURES (NOLOCK) AS AA
INNER JOIN SYS.SCHEMAS (NOLOCK) AS BB ON (AA.schema_id = BB.schema_id)
INNER JOIN SYS.COLUMNS (NOLOCK) AS CC ON (AA.object_id = CC.object_id)

For example:

USER_ID VARCHAR(200)

I only need the names of the column names a stored procedure returns and their data types. SQL Server version is 2014, if it matters.

Any ideas? Thanks.

Answer 1

Specifically for objects, there is a DMV called sys.dm_exec_describe_first_result_set_for_object which will describe the first result set if SQL Server can figure out what it should be (dynamic SQL, for example won't return a valid result).

Specifically for T-SQL or batch related items there is a different DMV and accompanied system stored procedure. The DMV is sys.dm_exec_describe_first_result_set and the stored procedure that parallels the dmv is sp_describe_first_result_set.

Answer 2

You should be able to use the new system stored procedure sp_describe_first_result_set for that - see the MSDN docs for details:

EXEC sp_describe_first_result_set N'YourStoredProcedureNameHere'

Answer 3

If you use Openquery to insert the results of the stored procedure into a temp table, you can query the columns for the temp table.

Note, you cannot use a @variable in Openquery, so you will need to run it through dynamic sql and an exec. The problem with that is now the temp table doesn't exist outside the dynamic query. But, if you put your temp table into a global query you can still access it. Then just query the system tables for the column info as you normally would.

    Create PROCEDURE TheProcedure                   
    AS                  
    BEGIN                   
        select 1 id,                
            'textstring' textstring,            
            convert(bit, 'true') boolean            

    END                 
    GO                  

    drop table ##test                   

    declare @query varchar(255)                 
    select @query =             'select * ' 
    select @query = @query +    'into ##test '              
    select @query = @query +    'from Openquery([' + @@SERVERNAME + '], ''exec misdb.dbo.TheProcedure'') '              

    EXEC(@query)                    

    select AC.name columnName,                  
        T.name dataType             
    from tempdb.Sys.columns AC                  
    join tempdb.sys.types T                 
        on AC.system_type_id = T.system_type_id             
    where object_id = object_id('tempdb.dbo.##test')
    order by AC.column_id

/*
columnName  dataType
----------  --------
id          int
textstring  varchar
boolean     bit
*/

Answer 4

For dynamic stored procedures, we include a parameter option to execute the query (and all related queries/CTEs with TOP 0 (e.g., select top 0 * from ). This way, the query runs fast, but still has the field structure.

For the final result query, we add TOP 0 along with an INTO #t1, so that the final code is:

EXEC sp_describe_first_result_set N'SELECT * from #t1'

This gives us the column information from the dynamic stored procedure.

Answer 5

Sidenote / Workaround: If you just need to know once (not to automate) and are using RedGate SQLPrompt (a must have for each SQL developer in my opinion), you could execute the procedure, mark a line in the result set, right click onto it and choose Script as Insert. This will create a CREATE TABLE #temptable statement plus the corresponding insert / drop.

I use this often, if I need the result in a procedure (as I guess you do).