Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Apr 10, 2000

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 sysobjects
WHERE 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."
Ravindra Okade
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date