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.

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:


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.


5 Answers 5


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.


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'
  • 2
    Doesn't work if the stored proc has temp tables.
    – Cryptc
    Dec 3, 2021 at 20:47

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                   
        select 1 id,                
            'textstring' textstring,            
            convert(bit, 'true') boolean            


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


    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
  • 3
    This can be useful for older versions of SQL Server, but for the OP, there's a lot of overhead and scaffolding here that make sys.dm_exec_describe... a much better option. Apr 15, 2016 at 2:16
  • 1
    I've got a customer who refuses to upgrade SQL Server. Your solution here is spot-on. Thank you!
    – STLDev
    Nov 22, 2020 at 3:40

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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.