Check for existence of temp table

MSSQL syntax for checking if a temporary table exists.

[Friday, September 23, 2011]

We were checking for the existence of a temporary table today and used the following syntax

Bad:

IF EXISTS( SELECT 1 FROM tempdb.sys.tables WHERE NAME LIKE '%MyTable%' )

But that doesn't work because multiple databases use the same name for their temporary table. So we used this instead:

Good:

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL 
    PRINT 'Exists.' 
ELSE 
    PRINT 'Does not exist.'

Posted by: Benjamin Felt

Search