SQL Server Table definition from stored procedure result set

Question

Is anyone aware of a way to create a table definition from a stored procedure result set?

I have a stored procedure which produces a result set with 30+ columns, I'd like to get this into a table without having to manually create all the table columns.

Is there a built in procedure that will dump out the column names and types..?

Thanks.

To avoid having to manually create the table, I'm just trying to save some time really ... I've had issues in the past using 'INSERT EXEC' where the columns returned in the stored procedure don't exactly match that of the table. It can be a real pain when these procedures return so many columns. — SuperBrook, Mar 19 '10 at 14:22

score 2 · Answer 1 · answered Nov 23 '18 at 23:07

Have you tried this?

DECLARE @sql NVARCHAR(MAX) = N'EXEC mydb.dbo.my_procedure;';

SELECT name, system_type_name, max_length, is_nullable FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);

It won't build the table for you, but you can quickly create it knowing the names and types of the column names.

score 0 · Answer 2 · answered Mar 19 '10 at 14:44

If this is a one time thing, you can try this (not in production!).

I usually just go to the child procedure and on the query that returns the result set, add INTO YourNewTable between the column select list and the FROM. Run the child procedure 1 time and then remove the change.

You can then go and look at the result set columns by looking at YourNewTable in SQl Server Management Studio, or any table meta data query like:

SELECT 
    * 
    FROM INFORMATION_SCHEMA.Columns 
    WHERE TABLE_NAME='YourNewTable'
    ORDER BY ORDINAL_POSITION

score 0 · Answer 3 · answered Jan 02 '20 at 12:36

0

It has been introduced this https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql?view=sql-server-ver15:

exec sp_describe_first_result_set N'mydb.dbo.my_sproc_name'

answered Jan 02 '20 at 12:36

SMagistri-Utmost

5
2

score 0 · Accepted Answer · answered Oct 03 '11 at 18:38

You "can" do it but I don't know if I'd recommend it:

EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT
  *
INTO
  #table
FROM
  OPENROWSET(
    'SQLNCLI',
    'SERVER=.;Trusted_Connection=yes',
    'EXEC StoredProcedureName'
  )

score 0 · Answer 5 · answered Mar 27 '12 at 14:59

in your stored procedure, you will need to perform a "SELECT INTO" with a "WHERE 1 = 0" so it just creates an empty table.

If you want to define the data types: convert or cast your column into that data type

if you want a NOT NULL constraint, then you must use in the select: isnull(yourcolumn,'123') as yourcolumn and then in the where clause put WHERE yourcolumn <> '123'

SQL Server Table definition from stored procedure result set

5 Answers5

Linked