Check Whether a Global Temporary Exists in a SQL Database

Checking whether a table exists in a Microsoft SQL Server database is easy. You can use this query:

 SELECT 'x'FROM sysobjectsWHERE type = 'U' and NAME = 'mytable'

But this query will not work while searching for global temporary tables. Global temporary tables are stored in tempdb.

Use this syntax for the search:

 DECLARE @temp_table VARCHAR(100)SET @temp_table = '##my_temp_table'IF NOT EXISTS (SELECT 'x'          FROM tempdb..sysobjects          WHERE type = 'U' and NAME = @temp_table)    PRINT 'temp table ' + @temp_table + ' does not exist'ELSE    PRINT 'temp table ' + @temp_table + ' exists.'

Note: You cannot search for local temporary tables (# prefix tables) in this way. This is because SQL Server appends a unique number to the name you supply. For example, if you specified “#temp,” the name in sysobjects would be something like “#temp____1234.”