Generate a Table Variable from Query Metadata

Introduction

Many SQL Server procedures require to create a table variable populated from a query. It could be a time consuming process to declare this variable or you may even not have access to the definitions of all tables and views in the query. The process can be automated to get correct column definitions.

Background

The solution contains two steps:

1. Create a temporary table based on the query (SELECT INTO)
2. Print this table definition

The first step is simple but you may need to make some changes in the query. For instance, the following query retrieving all employees in the Engineering department:

SELECT 
    ISNULL(p.[Title] + ' ', '') + p.[FirstName] + ' ' + ISNULL(p.[MiddleName] + ' ', '') + _
	p.[LastName] AS [Name]
    ,e.[JobTitle]
    ,edh.[StartDate] 
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] p 	ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh _
	ON e.[BusinessEntityID] = edh.[BusinessEntityID] 
    INNER JOIN [HumanResources].[Department] d ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE edh.EndDate IS NULL AND d.[Name] = 'Engineering'
ORDER BY p.[LastName], p.[FirstName]

You can change WHERE clause to WHERE 1 = 2 and omit ORDER BY clause because you need to create only the table structure. All columns in your query should have explicit names. If you miss "AS [Name]" here "SELECT INTO" in the script below will fail. It also fails if multiple columns in the query have the same name; you should use aliases in this case.

The second step was described in several articles, for instance here. I enhanced one of the scripts posted in this article and implemented it in my solution.

Using the Code

The following script prints the table variable declaration based on the query above:

USE [AdventureWorks2014]
GO

SET NOCOUNT ON;
SELECT a.* INTO #temp_table FROM -- Place your query below this line
(SELECT 
    ISNULL(p.[Title] + ' ', '') + p.[FirstName] + ' ' + _
	ISNULL(p.[MiddleName] + ' ', '') + p.[LastName] AS [Name]
    ,e.[JobTitle]
    ,edh.[StartDate] 
 FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] p 	ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh _
		ON e.[BusinessEntityID] = edh.[BusinessEntityID] 
    INNER JOIN [HumanResources].[Department] d ON edh.[DepartmentID] = d.[DepartmentID]
 WHERE 1 = 2) a 

DECLARE @descr varchar(8000) = 'DECLARE @table_var TABLE (' + char(10)
-- Next statement is required because #temp_table can be created in several sessions
DECLARE @table_name sysname = OBJECT_NAME(OBJECT_ID('tempdb..#temp_table'), DB_ID('tempdb'))
DECLARE @last_column_id int = (
    SELECT MAX(ORDINAL_POSITION) 
    FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = @table_name)

SELECT @descr = @descr + '   [' + COLUMN_NAME + '] ' + DATA_TYPE +
	CASE WHEN DATA_TYPE IN ('nvarchar', 'varbinary', 'varchar') AND _
		CHARACTER_MAXIMUM_LENGTH = -1 THEN '(max) '
		WHEN DATA_TYPE IN ('binary', 'char', 'nvarchar', 'varbinary', 'varchar') 
             THEN '(' + CONVERT(varchar(5), CHARACTER_MAXIMUM_LENGTH) + ') '
		WHEN DATA_TYPE IN ('decimal', 'numeric') 
             THEN '(' + CONVERT(varchar(2), NUMERIC_PRECISION) + ', ' + _
		CONVERT(varchar(2), NUMERIC_SCALE) + ') '
		WHEN DATA_TYPE IN ('datetime2', 'datetimeoffset', 'time')  
             THEN '(' + CONVERT(varchar(2), DATETIME_PRECISION) + ') '
		ELSE ' '
	END + IIF(IS_NULLABLE = 'NO', 'NOT NULL', 'NULL') + _
		IIF(ORDINAL_POSITION = @last_column_id, '', ',') + char(10)
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

SET @descr = @descr + ')'

DROP TABLE #temp_table

print @descr
GO

The output of this script is:

DECLARE @table_var TABLE (
    [Name] nvarchar(161) NOT NULL,
    [JobTitle] nvarchar(50) NOT NULL,
    [StartDate] date NOT NULL
)