or
sql-server
Just a learner imported from SE
Many times I need to write something like the following when dealing with SQL Server.

    create table #table_name
    (
        column1 int,
        column2 varchar(200)
        ...
    )
    
    insert into #table_name
    execute some_stored_procedure;

But create a table which has the exact syntax as the result of a stored procedure is a tedious task. For example, the result of [`sp_helppublication`][1] has 48 columns! I want to know if there is an easier way to do this?


  [1]: https://msdn.microsoft.com/en-us/library/ms189782.aspx
Top Answer
aaron bertrand imported from SE
In SQL Server 2012 and above, you can use [`sys.dm_exec_describe_first_result_set`](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-describe-first-result-set-transact-sql) locally, assuming the result set you are after is the *first* result:

    DECLARE @sql NVARCHAR(MAX) = N'';
    
    SELECT @sql += ',' + CHAR(13) + CHAR(10) + CHAR(9)
    	+ name + ' ' + system_type_name
    	FROM sys.dm_exec_describe_first_result_set('sp_who', NULL, 1);
    
    SELECT @sql = N'CREATE TABLE #f
    (' + STUFF(@sql, 1, 1, N'') + '
    );';
    
    PRINT @sql;

Result:

    CREATE TABLE #f
    (
    	spid smallint,
    	ecid smallint,
    	status nchar(30),
    	loginame nvarchar(128),
    	hostname nchar(128),
    	blk char(5),
    	dbname nvarchar(128),
    	cmd nchar(16),
    	request_id int
    );

Note there is a limitation: if your stored procedure creates #temp tables, the metadata functionality does not work. This is why I did not use sp_who2. :-)
Answer #2
Martin Smith
If the procedure just returns one result set and the [ad hoc distributed queries][1] option is enabled.

    SELECT * 
    INTO #T 
    FROM OPENROWSET('SQLNCLI', 
                    'Server=(local)\MSSQL2008;Trusted_Connection=yes;',
                     'SET FMTONLY OFF;EXEC sp_who')
     

Or you can set up a loopback linked server and use that instead.

    EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                            @provider = 'SQLNCLI', @datasrc = @@servername
    
    SELECT *
    INTO  #T
    FROM OPENQUERY(LOCALSERVER, 
                   'SET FMTONLY OFF;
                   EXEC sp_who')

  [1]: http://msdn.microsoft.com/en-us/library/ms187569.aspx
What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.