Posted on

Checking for the Existence of Global Temp Table in TSQL

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.

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

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

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

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

 

2 thoughts on “Checking for the Existence of Global Temp Table in TSQL

  1. Hi Kendra,

    What I normally do to overcome this issue is to use current date as name for the global temp tables I create today, and only have to remember what it was the latest. For instance today my first global temp table will be named ##tmp170510 if I need to create additional ones they will be suffixed with letters (##tmp170510a, ##tmp170510b, etc) or if there is a query involving many temporal tables for different report stages I use meaningful suffixes (##tmp170510Summary, ##tmp170510ActiveCustomers, and so on)

    This way I only have to worry about the collisions I could have today

  2. This tip is not limited to global temporary tables and works with local too and works correct, when you use the same local temp table in different sessions.

    Session 1:
    CREATE TABLE #tmp (id INT IDENTITY);
    SELECT OBJECT_ID(‘tempdb.dbo.#tmp’); — returns an ID

    Session 2:
    SELECT OBJECT_ID(‘tempdb.dbo.#tmp’); — returns NULL
    CREATE TABLE #tmp (id INT IDENTITY);
    SELECT OBJECT_ID(‘tempdb.dbo.#tmp’); — returns an ID (but not the same as in Session 1)

    Session 1:
    DROP TABLE #tmp;
    SELECT OBJECT_ID(‘tempdb.dbo.#tmp’); — returns NULL

    Session 2:
    SELECT OBJECT_ID(‘tempdb.dbo.#tmp’); — returns an ID
    DROP TABLE #tmp;
    SELECT OBJECT_ID(‘tempdb.dbo.#tmp’); — returns NULL

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.