Get Column Output List for Query

Applicability:

                 SQL Server 2000:        N/A
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Not Tested          

Credits:

Author:Phil Factor/ChillyDBA
Date:    5 Jun 2020

Description

There is a little-known DMV called sys.dm_exec_describe_first_result_set  that returns a list of columns for any valid specified source query string (SP call, TSQL or Dynamic TSQL)

This function has been wrapped in 2 very helpful pieces of code:
  1. Basic - produces an ordered list of columns and data types.  Can be onwardly used to dynamically create TSQL to process the results
  2. Function - produces a full CREATE TABLE statement.  This can be a physical or #Temp table, and can include a DROP TABLE statement if required.  Extremely useful for dynamic ETL processing, or even just temporarily storing the results of some system SPs or XPs  (ex sp_spaceused)

Code

Get list of output columns for any valid query:

DECLARE @SourceQuery NVARCHAR(MAX) SELECT @SourceQuery = N'SELECT * FROM serverstatus.serverlist;' ;WITH cte_Component_Metadata AS ( SELECT name AS Column_Name ,CASE WHEN is_identity_column = 1 THEN UPPER(system_type_name) + ' (IDENTITY(1,1)' ELSE UPPER(system_type_name) END AS Column_DataType ,CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END AS Column_Nullability ,column_ordinal AS Column_Ordinal FROM sys.dm_exec_describe_first_result_set (@SourceQuery, NULL, 0) AS f ) ,cte_DataLengths AS ( SELECT MAX(LEN(Column_Name)) + 10 AS ColumnNameLength ,MAX(LEN(Column_DataType)) + 10 AS ColumnTypeLength ,MAX(LEN(Column_Nullability)) + 10 AS ColumnNullabilityLength FROM cte_Component_Metadata ) SELECT @@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,Column_Name ,Column_DataType ,Column_Nullability ,Column_Ordinal ,CASE WHEN Column_Ordinal = 1 THEN ' ' ELSE ',' END + Column_Name + REPLICATE(' ', ColumnNameLength - LEN(Column_Name)) + Column_DataType + REPLICATE(' ', ColumnTypeLength - LEN(Column_DataType)) + Column_Nullability + REPLICATE(' ', ColumnNullabilityLength - LEN(Column_Nullability)) AS Creation_Text FROM cte_Component_Metadata CROSS APPLY cte_DataLengths ORDER BY Column_Ordinal





Function to produce a CREATE TABLE statement to hold the results of any valid query:

--SELECT dbo.fn_GetTableCreationStatement('sys.objects', 'TEMP_SYSOBJECTS', 1, 1, 0) --SELECT dbo.fn_GetTableCreationStatement('sys.objects', 'TEMP_SYSOBJECTS', 1, 1, 1) --SELECT dbo.fn_GetTableCreationStatement('sys.objects', 'TEMP_SYSOBJECTS', 1, 0, 0) --SELECT dbo.fn_GetTableCreationStatement('sys.objects', 'TEMP_SYSOBJECTS', 1, 0, 1) 

--SELECT dbo.fn_GetTableCreationStatement('SELECT * FROM sys.objects', 'TEMP_SYSOBJECTS', 0, 0, 1) --the most common use -- return a temp table definition for a query output to be placed into --SELECT dbo.fn_GetTableCreationStatement('SELECT * FROM sys.objects WHERE name LIKE ''%s%'' ', 'TEMP_SYSOBJECTS', 0, 0, 1) --SELECT dbo.fn_GetTableCreationStatement('SELECT * FROM sys.objects cross apply sysindexes ', 'TEMP_SYSOBJECTS', 0, 0, 1) IF (SELECT OBJECT_ID ('dbo.fn_GetTableCreationStatement')) IS NOT NULL DROP FUNCTION dbo.fn_GetTableCreationStatement GO CREATE FUNCTION dbo.fn_GetTableCreationStatement ( @SourceText NVARCHAR(MAX) ,@ResultTableName NVARCHAR(MAX) ,@IsTable INT ,@IsDropRequired INT ,@IsTempTable INT ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE --internal variables @SourceQuery NVARCHAR(MAX) ,@Command NVARCHAR(MAX) ,@CRLF NVARCHAR(10) ,@TAB NVARCHAR(10) -- -- input parameters --,@SourceText NVARCHAR(MAX) --,@ResultTableName NVARCHAR(MAX) --,@IsTable INT --,@IsDropRequired INT --,@IsTempTable INT SELECT @Command = '' ,@CRLF = CHAR(13) ,@TAB = CHAR(9) --,@SourceText = 'sys.objects' --,@ResultTableName = 'TEMP_SYSOBJECTS' --,@IsTable = 1 --,@IsDropRequired = 1 --,@IsTempTable = 0 --override drop flag - always supply the drop statement for temp tables IF @IsTempTable = 1 SELECT @IsDropRequired = 1 -- convert the output table name to temp table if required IF @IsTempTable = 1 AND LEFT(@ResultTableName,1) <> '#' SELECT @ResultTableName = '#' + @ResultTableName IF @IsTable = 1 SELECT @SourceQuery = N'SELECT * FROM ' + @SourceText ELSE SELECT @SourceQuery = @SourceText -- start the creation of the output text -- drop statememt IF @IsDropRequired = 1 BEGIN SELECT @Command = @Command + 'IF (SELECT OBJECT_ID(' + @ResultTableName + ')) IS NOT NULL' + @CRLF + @TAB + 'DROP TABLE ' + @ResultTableName + @CRLF + 'GO ' + @CRLF + @CRLF END --create statement SELECT @Command = @Command + 'CREATE TABLE ' + @ResultTableName + @CRLF + '(' ;WITH cte_Component_Metadata AS ( SELECT name AS Column_Name ,CASE WHEN is_identity_column = 1 THEN UPPER(system_type_name) + ' (IDENTITY(1,1)' ELSE UPPER(system_type_name) END AS Column_DataType ,CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END AS Column_Nullability ,column_ordinal AS Column_Ordinal FROM sys.dm_exec_describe_first_result_set (@SourceQuery, NULL, 0) AS f ) ,cte_DataLengths AS ( SELECT MAX(LEN(Column_Name)) + 10 AS ColumnNameLength ,MAX(LEN(Column_DataType)) + 10 AS ColumnTypeLength ,MAX(LEN(Column_Nullability)) + 10 AS ColumnNullabilityLength FROM cte_Component_Metadata ) ,cte_Results AS ( SELECT Column_Name ,Column_DataType ,Column_Nullability ,Column_Ordinal ,CASE WHEN Column_Ordinal = 1 THEN ' ' ELSE ',' END + Column_Name + REPLICATE(' ', ColumnNameLength - LEN(Column_Name)) + Column_DataType + REPLICATE(' ', ColumnTypeLength - LEN(Column_DataType)) + Column_Nullability + REPLICATE(' ', ColumnNullabilityLength - LEN(Column_Nullability)) AS Creation_Text FROM cte_Component_Metadata CROSS APPLY cte_DataLengths ) SELECT @Command = @Command + @CRLF + @TAB + Creation_Text FROM cte_Results ORDER BY Column_Ordinal SELECT @Command = @Command + @CRLF + ')' RETURN @Command END






Comments