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