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

2019-03-14 · by Hannah Vernon · in t-sql, tools

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 ¹ system dynamic management function, or DMF, to generate a CREATE TABLE statement.

/*
    Builds a CREATE TABLE statement to create a temporary table with the required columns
    By: Max Vernon
*/
SET NOCOUNT ON;
DECLARE @tempTableName sysname;
DECLARE @cmd nvarchar(max);

SET @tempTableName = N'#someTable';
SET @cmd = N'<replace this with your EXEC your_stored_procedure>';

DECLARE @createTableStmt nvarchar(max);
DECLARE @maxColumnNameLength int;
DECLARE @maxTypeNameLength int;
SELECT @maxColumnNameLength = MAX(LEN(rs.name))
    , @maxTypeNameLength = MAX(LEN(rs.system_type_name))
FROM sys.dm_exec_describe_first_result_set(@cmd, NULL, NULL) rs;

SET @createTableStmt = STUFF(
    (
        SELECT N', ' + rs.name 
            + N' ' + REPLICATE(N' ', @maxColumnNameLength - LEN(rs.name)) 
            + rs.system_type_name + N' ' + REPLICATE(N' ', @maxTypeNameLength - LEN(rs.system_type_name)) 
            + CASE WHEN rs.collation_name IS NOT NULL AND rs.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation') 
                THEN N'COLLATE ' + rs.collation_name + N' ' ELSE N'' END
            + CASE WHEN rs.is_nullable = 1 THEN N'NULL' ELSE N'NOT NULL' END
        FROM sys.dm_exec_describe_first_result_set(@cmd, NULL, NULL) rs 
        ORDER BY rs.column_ordinal
        FOR XML PATH('')
    ), 1, 2, N'');
SET @createTableStmt = 'IF OBJECT_ID(N''' + @tempTableName + N''', N''U'') IS NOT NULL
BEGIN
    DROP TABLE ' + @tempTableName + N';
END
CREATE TABLE ' + @tempTableName + N'
(
      ' + REPLACE(@createTableStmt, N', ', NCHAR(13) + NCHAR(10) + N'    , ') + N'
);
';

IF LEN(@CreateTableStmt) > 4000 
BEGIN
    DECLARE @CurrentLen int;
    SET @CurrentLen = 1;
    WHILE @CurrentLen <= LEN(@CreateTableStmt)
    BEGIN
        PRINT SUBSTRING(@CreateTableStmt, @CurrentLen, 4000);
        SET @CurrentLen = @CurrentLen + 4000;
    END
    RAISERROR (N'Output is chunked into 4,000 char pieces - look for errant line endings!', 14, 1);
END
ELSE
BEGIN
    PRINT @createTableStmt;
END

Builds a CREATE TABLE statement to create a temporary table with the required columns

By: Max Vernon

SET NOCOUNT ON;

DECLARE @tempTableName sysname;

DECLARE @cmd nvarchar(max);

SET @tempTableName = N'#someTable';

SET @cmd = N'<replace this with your EXEC your_stored_procedure>';

DECLARE @createTableStmt nvarchar(max);

DECLARE @maxColumnNameLength int;

DECLARE @maxTypeNameLength int;

SELECT @maxColumnNameLength = MAX(LEN(rs.name))

, @maxTypeNameLength = MAX(LEN(rs.system_type_name))

FROM sys.dm_exec_describe_first_result_set(@cmd, NULL, NULL) rs;

SET @createTableStmt = STUFF(

(

SELECT N', ' + rs.name

+ N' ' + REPLICATE(N' ', @maxColumnNameLength - LEN(rs.name))

+ rs.system_type_name + N' ' + REPLICATE(N' ', @maxTypeNameLength - LEN(rs.system_type_name))

+ CASE WHEN rs.collation_name IS NOT NULL AND rs.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation')

THEN N'COLLATE ' + rs.collation_name + N' ' ELSE N'' END

+ CASE WHEN rs.is_nullable = 1 THEN N'NULL' ELSE N'NOT NULL' END

FROM sys.dm_exec_describe_first_result_set(@cmd, NULL, NULL) rs

ORDER BY rs.column_ordinal

FOR XML PATH('')

), 1, 2, N'');

SET @createTableStmt = 'IF OBJECT_ID(N''' + @tempTableName + N''', N''U'') IS NOT NULL

BEGIN

DROP TABLE ' + @tempTableName + N';

END

CREATE TABLE ' + @tempTableName + N'

(

' + REPLACE(@createTableStmt, N', ', NCHAR(13) + NCHAR(10) + N' , ') + N'

);

IF LEN(@CreateTableStmt) > 4000

BEGIN

DECLARE @CurrentLen int;

SET @CurrentLen = 1;

WHILE @CurrentLen <= LEN(@CreateTableStmt)

BEGIN

PRINT SUBSTRING(@CreateTableStmt, @CurrentLen, 4000);

SET @CurrentLen = @CurrentLen + 4000;

END

RAISERROR (N'Output is chunked into 4,000 char pieces - look for errant line endings!', 14, 1);

END

ELSE

BEGIN

PRINT @createTableStmt;

END

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

USE tempdb;
IF OBJECT_ID(N'dbo.outputTest', N'P') IS NOT NULL
DROP PROCEDURE dbo.outputTest;
GO
CREATE PROCEDURE dbo.outputTest
(
    @column_i int
    , @column_j varchar(30)
    , @column_k char(6) 
)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT v.i
        , v.j
        , v.k
    FROM (VALUES (@column_i, @column_j, @column_k)) v(i, j, k);
END
GO

USE tempdb;

IF OBJECT_ID(N'dbo.outputTest', N'P') IS NOT NULL

DROP PROCEDURE dbo.outputTest;

CREATE PROCEDURE dbo.outputTest

(

@column_i int

, @column_j varchar(30)

, @column_k char(6)

)

BEGIN

SET NOCOUNT ON;

SELECT v.i

, v.j

, v.k

FROM (VALUES (@column_i, @column_j, @column_k)) v(i, j, k);

END

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:

/*
    Builds a CREATE TABLE statement to create a temporary table with the required columns
    By: Max Vernon
*/
SET NOCOUNT ON;
DECLARE @tempTableName sysname;
DECLARE @cmd nvarchar(max);

SET @tempTableName = N'#someTable';
SET @cmd = N'EXEC dbo.outputTest @column_i = 1, @column_j = NULL, @column_k = NULL;';

DECLARE @createTableStmt nvarchar(max);
DECLARE @maxColumnNameLength int;
DECLARE @maxTypeNameLength int;
SELECT @maxColumnNameLength = MAX(LEN(rs.name))
    , @maxTypeNameLength = MAX(LEN(rs.system_type_name))
FROM sys.dm_exec_describe_first_result_set(@cmd, NULL, NULL) rs;

SET @createTableStmt = STUFF(
    (
        SELECT N', ' + rs.name 
            + N' ' + REPLICATE(N' ', @maxColumnNameLength - LEN(rs.name)) 
            + rs.system_type_name + N' ' + REPLICATE(N' ', @maxTypeNameLength - LEN(rs.system_type_name)) 
            + CASE WHEN rs.collation_name IS NOT NULL AND rs.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation') 
                THEN N'COLLATE ' + rs.collation_name + N' ' ELSE N'' END
            + CASE WHEN rs.is_nullable = 1 THEN N'NULL' ELSE N'NOT NULL' END
        FROM sys.dm_exec_describe_first_result_set(@cmd, NULL, NULL) rs 
        ORDER BY rs.column_ordinal
        FOR XML PATH('')
    ), 1, 2, N'');
SET @createTableStmt = 'IF OBJECT_ID(N''' + @tempTableName + N''', N''U'') IS NOT NULL
BEGIN
    DROP TABLE ' + @tempTableName + N';
END
CREATE TABLE ' + @tempTableName + N'
(
      ' + REPLACE(@createTableStmt, N', ', NCHAR(13) + NCHAR(10) + N'    , ') + N'
);
';

IF LEN(@CreateTableStmt) > 4000 
BEGIN
    DECLARE @CurrentLen int;
    SET @CurrentLen = 1;
    WHILE @CurrentLen <= LEN(@CreateTableStmt)
    BEGIN
        PRINT SUBSTRING(@CreateTableStmt, @CurrentLen, 4000);
        SET @CurrentLen = @CurrentLen + 4000;
    END
    RAISERROR (N'Output is chunked into 4,000 char pieces - look for errant line endings!', 14, 1);
END
ELSE
BEGIN
    PRINT @createTableStmt;
END

Builds a CREATE TABLE statement to create a temporary table with the required columns

By: Max Vernon

SET NOCOUNT ON;

DECLARE @tempTableName sysname;

DECLARE @cmd nvarchar(max);

SET @tempTableName = N'#someTable';

SET @cmd = N'EXEC dbo.outputTest @column_i = 1, @column_j = NULL, @column_k = NULL;';

DECLARE @createTableStmt nvarchar(max);

DECLARE @maxColumnNameLength int;

DECLARE @maxTypeNameLength int;

SELECT @maxColumnNameLength = MAX(LEN(rs.name))

, @maxTypeNameLength = MAX(LEN(rs.system_type_name))

FROM sys.dm_exec_describe_first_result_set(@cmd, NULL, NULL) rs;

SET @createTableStmt = STUFF(

(

SELECT N', ' + rs.name

+ N' ' + REPLICATE(N' ', @maxColumnNameLength - LEN(rs.name))

+ rs.system_type_name + N' ' + REPLICATE(N' ', @maxTypeNameLength - LEN(rs.system_type_name))

+ CASE WHEN rs.collation_name IS NOT NULL AND rs.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation')

THEN N'COLLATE ' + rs.collation_name + N' ' ELSE N'' END

+ CASE WHEN rs.is_nullable = 1 THEN N'NULL' ELSE N'NOT NULL' END

FROM sys.dm_exec_describe_first_result_set(@cmd, NULL, NULL) rs

ORDER BY rs.column_ordinal

FOR XML PATH('')

), 1, 2, N'');

SET @createTableStmt = 'IF OBJECT_ID(N''' + @tempTableName + N''', N''U'') IS NOT NULL

BEGIN

DROP TABLE ' + @tempTableName + N';

END

CREATE TABLE ' + @tempTableName + N'

(

' + REPLACE(@createTableStmt, N', ', NCHAR(13) + NCHAR(10) + N' , ') + N'

);

IF LEN(@CreateTableStmt) > 4000

BEGIN

DECLARE @CurrentLen int;

SET @CurrentLen = 1;

WHILE @CurrentLen <= LEN(@CreateTableStmt)

BEGIN

PRINT SUBSTRING(@CreateTableStmt, @CurrentLen, 4000);

SET @CurrentLen = @CurrentLen + 4000;

END

RAISERROR (N'Output is chunked into 4,000 char pieces - look for errant line endings!', 14, 1);

END

ELSE

BEGIN

PRINT @createTableStmt;

END

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

^{1 –}Microsoft Docs for sys.dm_exec_describe_first_result_set

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

Related

Leave a ReplyCancel reply

Search

Categories

Pages

Meta