SQL Server Table definition from stored procedure result set

Question

Hi,

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.

Answer 1

Duplicate of http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure

Looks like it is possible, which is good for you!

Answer 2

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