CREATE TABLE Script for Temp Table

Following up on the earlier queries that interrogated INFORMATION_SCHEMA.TABLES to get metadata on temp tables, this script builds on those to produce a CREATE TABLE statement. I find this useful for capturing the table-typed output of a stored procedure, especially if I need to capture that output in an INSERT..EXEC statement.

DECLARE @sql varchar(max); SET @sql = 'CREATE TABLE #capture_output (' + CHAR(13) + CHAR(10)
; WITH struct AS (
SELECT *
FROM TempDb.INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = OBJECT_NAME(OBJECT_ID('TempDb..#tmp'), (SELECT Database_Id FROM SYS.DATABASES WHERE Name = 'TempDb'))
)
SELECT
@sql += CHAR(9) + ',' + QUOTENAME(struct.[COLUMN_NAME]) + ' ' + struct.[DATA_TYPE]
+ ISNULL('(' + CASE struct.[DATA_TYPE]
WHEN 'varchar' THEN CONVERT(varchar, struct.[CHARACTER_MAXIMUM_LENGTH])
WHEN 'decimal' THEN CONVERT(varchar, struct.[NUMERIC_PRECISION]) + ', ' + CONVERT(varchar, struct.[NUMERIC_SCALE])
END
+ ')', '')
+ CHAR(13) + CHAR(10)
FROM struct
SET @sql = REPLACE(@sql, '(' + CHAR(13) + CHAR(10) + CHAR(9) + ',', '(' + CHAR(13) + CHAR(10) + CHAR(9)) + ')'
PRINT @sql