List columns in a temporary table
DECLARE @tempTable NVARCHAR(128) = '#tmp'
;WITH cols AS (
SELECT
'[' + c.COLUMN_NAME + ']' AS ColumnName
, c.ORDINAL_POSITION
, UPPER(c.DATA_TYPE) AS DATA_TYPE
, CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) AS CHARACTER_MAXIMUM_LENGTH
, CAST(c.CHARACTER_OCTET_LENGTH AS VARCHAR) AS CHARACTER_OCTET_LENGTH
FROM tempdb.sys.objects o
INNER JOIN tempdb.sys.schemas s ON s.[schema_id] = o.[schema_id]
INNER JOIN tempdb.INFORMATION_SCHEMA.COLUMNS c
ON c.TABLE_SCHEMA = s.[name]
AND c.TABLE_NAME = o.[name]
WHERE o.[object_id] = OBJECT_ID('tempdb..' + @tempTable)
)
SELECT
cols.ColumnName
, ', ' + cols.ColumnName + ' ' + cols.DATA_TYPE
+ CASE
WHEN cols.DATA_TYPE LIKE '%CHAR' THEN '(' + cols.CHARACTER_MAXIMUM_LENGTH + ')'
WHEN cols.DATA_TYPE LIKE '%BINARY' THEN '(' + cols.CHARACTER_OCTET_LENGTH + ')'
ELSE ''
END
, ', ' + LEFT(cols.ColumnName + REPLICATE(' ', ml.[MaxLength]), ml.[MaxLength])
+ ' -- ' + UPPER(cols.DATA_TYPE)
FROM cols
CROSS JOIN (SELECT MAX(LEN(ColumnName)) AS [MaxLength] FROM cols) ml
ORDER BY cols.ORDINAL_POSITION