sql-server add tag
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 (imported from SE)
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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.