Build a CREATE TABLE statement from the output of a Stored Procedure with dm_exec_describe_first_result_set

Day-to-day database administration and development on SQL Server will often require creating a temporary table to hold the output of some stored procedure. The code below uses the dm_exec_describe_first_result_set 1 system dynamic management function, or DMF, to produce a CREATE TABLE statement.

To show how this works, imagine you want to capture the output of the following stored procedure into a temporary table for further processing:

You could manually type out the CREATE TABLE statement, or you could simply run the above script with the name of the dbo.outputTest procedure, as in:

The nicely formatted CREATE TABLE output looks like:

IF OBJECT_ID(N'#someTable', N'U') IS NOT NULL
BEGIN
    DROP TABLE #someTable;
END
CREATE TABLE #someTable
(
      i int         NULL
    , j varchar(30) NULL
    , k char(6)     NULL
);

You can now simply copy-and-paste the output into a new query window, and hey-presto – a nice temporary table that is completely compatible with the output of the dbo.outputTest stored proc.

This is a pretty simple example, however for really complex stored procedures that output a lot of detail, this can be invaluable.

Let me know if you think this post is valuable, or if you have a question or spot a problem.

This post is part of our series on T-SQL and is included in our list of Tools.

Here’s a great piece of art by Salvador Dalí.

Disintegration of Persistence Of Memory

    Disintegration of Persistence Of Memory


1 – Microsoft Docs for sys.dm_exec_describe_first_result_set