Every now and again, I need use a global temporary table for some testing or demo code.

Each time I do, I stumble a little bit when it comes to checking for the existence of the global temp table, in order to make my code re-runnable.

Check for Global Temp Table Object ID

Here’s the TSQL to check for the object_id of a global temporary table:

SELECT OBJECT_ID('tempdb..##Temp');
GO

Drop Global Temp Table If It Exists

So if you’d like to drop the global temp table if it exists, you can do this:

IF OBJECT_ID('tempdb..##Temp') IS NOT NULL
  DROP TABLE ##Temp;
GO

Truncate or Create Global Temp Table

Or if you’d like to truncate it if it exists, and create it if it doesn’t exist, you can do this:

IF OBJECT_ID('tempdb..##Temp') IS NOT NULL
  TRUNCATE TABLE ##Temp;
ELSE
  CREATE TABLE ##Temp (
    RecordID INT IDENTITY(1,1),
    CharColumn CHAR(500) NOT NULL,
    CONSTRAINT pk_UserDatabaseTablePK  PRIMARY KEY CLUSTERED (RecordID)
  )
GO