devxlogo

Check Whether a Global Temporary Exists in a SQL Database

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.”

See also  How IoT Tech Will Transform Logistics
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist