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(SELECTname AS Column_Name,CASEWHEN is_identity_column = 1 THEN UPPER(system_type_name) + ' (IDENTITY(1,1)'ELSE UPPER(system_type_name)END AS Column_DataType,CASEWHEN is_nullable=1 THEN ' NULL'ELSE ' NOT NULL'END AS Column_Nullability,column_ordinal AS Column_OrdinalFROM sys.dm_exec_describe_first_result_set (@SourceQuery, NULL, 0) AS f),cte_DataLengths AS(SELECTMAX(LEN(Column_Name)) + 10 AS ColumnNameLength,MAX(LEN(Column_DataType)) + 10 AS ColumnTypeLength,MAX(LEN(Column_Nullability)) + 10 AS ColumnNullabilityLengthFROM cte_Component_Metadata)SELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,Column_Name,Column_DataType,Column_Nullability,Column_Ordinal,CASEWHEN 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_TextFROM cte_Component_MetadataCROSS APPLY cte_DataLengthsORDER 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 NULLDROP FUNCTION dbo.fn_GetTableCreationStatementGOCREATE FUNCTION dbo.fn_GetTableCreationStatement( @SourceText NVARCHAR(MAX),@ResultTableName NVARCHAR(MAX),@IsTable INT,@IsDropRequired INT,@IsTempTable INT)RETURNS NVARCHAR(MAX)ASBEGINDECLARE--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 = 1SELECT @IsDropRequired = 1-- convert the output table name to temp table if required IF @IsTempTable = 1 AND LEFT(@ResultTableName,1) <> '#'SELECT @ResultTableName = '#' + @ResultTableNameIF @IsTable = 1SELECT @SourceQuery = N'SELECT * FROM ' + @SourceTextELSESELECT @SourceQuery = @SourceText-- start the creation of the output text -- drop statememt IF @IsDropRequired = 1BEGINSELECT @Command = @Command+ 'IF (SELECT OBJECT_ID(' + @ResultTableName + ')) IS NOT NULL'+ @CRLF + @TAB + 'DROP TABLE ' + @ResultTableName+ @CRLF + 'GO '+ @CRLF+ @CRLFEND--create statement SELECT @Command = @Command+ 'CREATE TABLE ' + @ResultTableName+ @CRLF + '(';WITH cte_Component_Metadata AS(SELECTname AS Column_Name,CASEWHEN is_identity_column = 1 THEN UPPER(system_type_name) + ' (IDENTITY(1,1)'ELSE UPPER(system_type_name)END AS Column_DataType,CASEWHEN is_nullable=1 THEN ' NULL'ELSE ' NOT NULL'END AS Column_Nullability,column_ordinal AS Column_OrdinalFROM sys.dm_exec_describe_first_result_set (@SourceQuery, NULL, 0) AS f),cte_DataLengths AS(SELECTMAX(LEN(Column_Name)) + 10 AS ColumnNameLength,MAX(LEN(Column_DataType)) + 10 AS ColumnTypeLength,MAX(LEN(Column_Nullability)) + 10 AS ColumnNullabilityLengthFROM cte_Component_Metadata),cte_Results AS(SELECTColumn_Name,Column_DataType,Column_Nullability,Column_Ordinal,CASEWHEN 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_TextFROM cte_Component_MetadataCROSS APPLY cte_DataLengths)SELECT @Command = @Command + @CRLF + @TAB + Creation_TextFROM cte_ResultsORDER BY Column_OrdinalSELECT @Command = @Command+ @CRLF + ')'RETURN @CommandEND