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