Wednesday, October 10, 2012

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.

No comments:

Post a Comment