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:
Basic - produces an ordered list of columns and data types. Can be onwardly used to dynamically create TSQL to process the results
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