Temp Table Structure

Related to the post on getting the name of a temp table, here's the code to get the structure:

SELECT * FROM TempDb.INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = OBJECT_NAME(OBJECT_ID('TempDb..#x'), (SELECT Database_Id FROM SYS.DATABASES WHERE Name = 'TempDb'))

I'm using this to create a stored proc to examine the contents of a temp table column-by-column, and record values that violate assumptions (e.g., is null), in an audit table. I'm adding this audit to several reporting procs, so creating this audit proc will save me a lot of work.

To get the same results, but in a format you can use to build a SELECT:

DECLARE @sql varchar(max) = 'SELECT' + CHAR(13) + CHAR(10)

SELECT @sql += ',' + QUOTENAME([COLUMN_NAME]) + CHAR(13) + CHAR(10)

FROM TempDb.INFORMATION_SCHEMA.COLUMNS

WHERE [TABLE_NAME] = OBJECT_NAME(OBJECT_ID('TempDb..#x'), (SELECT Database_Id FROM SYS.DATABASES WHERE Name = 'TempDb'))

PRINT @sql