Share via

How to get the table structure of the result from a SP.

Lylyy 380 Reputation points
2023-12-29T03:27:51+00:00

I need to create a new table based on the output from one SP. The SP output have lots of columns.

Is there any function in SSMS like generate create script from the output result?

SQL Server | Other
0 comments
Accepted answer
  1. LiHongMSFT-4306 31,571 Reputation points
    2023-12-29T03:53:24.8933333+00:00

    Hi @Lylyy

    You could insert the output into a new table.

    If you are able to modify the SP, then you could add a SELECT column-list INTO NewTable, after the new table created you might remove the statement.

    If not, you could use OPENROWSET which exists several security risks:

    SELECT * 
INTO New_Table
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC YourSP')

    You might also see this similar thread for more workarounds: Insert results of a stored procedure into a temporary table.

    Best regards,

    Cosmog Hong

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 47,516 Reputation points
    2023-12-29T06:14:00.2533333+00:00

    Is there any function in SSMS like generate create script from the output result?

    There is one in T-SQL, see sys.dm_exec_describe_first_result_set (Transact-SQL).

    Keep in mind, a SP can return 0-n resultset and them with dynamic/different structures.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.