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

Create SQL Column with Variable Default

I wanted to create an integer column in a metadata table that would prioritize the rows of the table (this is for column [execution_order]), and for ease-of-use I wanted inserts into the table to default to a unique value. Although this sounds a lot like an identity column, I wanted it to be nullable, and not necessarily unique.

This is what I came up with:

CREATE TABLE [dbo].[metadata] (
[metadata_id] int NOT NULL identity(1,1)
,[project_id] int NOT NULL
,[descr] varchar(100) NOT NULL
,[delete_stmt] varchar(max) NOT NULL
,[execution_order] int NULL
,[insert_date] datetime NOT NULL
,[insert_userid] varchar(50) NOT NULL
,[is_active] bit NOT NULL
,CONSTRAINT pk_dbo_metadata PRIMARY KEY ([metadata_id])
)
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__insert_date
DEFAULT(GETDATE()) FOR [insert_date]
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__insert_userid
DEFAULT(SUSER_NAME()) FOR [insert_userid]
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__is_active
DEFAULT(1) FOR [is_active]
GO

ALTER TABLE [dbo].[metadata]
ADD CONSTRAINT df_dbo_metadata__execution_order
DEFAULT((IDENT_CURRENT('[db].[dbo].[metadata]')) * 100) FOR [execution_order]
GO