7

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.

12

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.

8

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

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
*/
  • 2
    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. – Aaron Bertrand Apr 15 '16 at 2:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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