XML Feeds
I see more and more people asking how to check if a temporary table exists. How do you check if a temp table exists?
You can use IF OBJECT_ID('tempdb..#temp') IS NOT NULL Let's see how it works
tsql
- --Create table
- USE Norhtwind
- GO
- CREATE TABLE #temp(id INT)
- --Check if it exists
- IF OBJECT_ID('tempdb..#temp') IS NOT NULL
- BEGIN
- PRINT '#temp exists!'
- END
- ELSE
- BEGIN
- PRINT '#temp does not exist!'
- END
- --Another way to check with an undocumented optional second parameter
- IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
- BEGIN
- PRINT '#temp exists!'
- END
- ELSE
- BEGIN
- PRINT '#temp does not exist!'
- END
- --Don't do this because this checks the local DB and will return does not exist
- IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
- BEGIN
- PRINT '#temp exists!'
- END
- ELSE
- BEGIN
- PRINT '#temp does not exist!'
- END
- --unless you do something like this
- USE tempdb
- GO
- --Now it exists again
- IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
- BEGIN
- PRINT '#temp exists!'
- END
- ELSE
- BEGIN
- PRINT '#temp does not exist!'
- END
- --let's go back to Norhtwind again
- USE Norhtwind
- GO
- --Check if it exists
- IF OBJECT_ID('tempdb..#temp') IS NOT NULL
- BEGIN
- PRINT '#temp exists!'
- END
- ELSE
- BEGIN
- PRINT '#temp does not exist!'
- END
now open a new window from Query Analyzer (CTRL + N) and run this code again
tsql
- --Check if it exists
- IF OBJECT_ID('tempdb..#temp') IS NOT NULL
- BEGIN
- PRINT '#temp exists!'
- END
- ELSE
- BEGIN
- PRINT '#temp does not exist!'
- END
It doesn't exist and that is correct since it's a local temp table not a global temp table
Well let's test that statement
tsql
- --create a global temp table
- CREATE TABLE ##temp(id INT) --Notice the 2 pound signs, that's how you create a global variable
- --Check if it exists
- IF OBJECT_ID('tempdb..##temp') IS NOT NULL
- BEGIN
- PRINT '##temp exists!'
- END
- ELSE
- BEGIN
- PRINT '##temp does not exist!'
- END
It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)
tsql
- --Check if it exists
- IF OBJECT_ID('tempdb..##temp') IS NOT NULL
- BEGIN
- PRINT '##temp exists!'
- END
- ELSE
- BEGIN
- PRINT '##temp does not exist!'
- END
And yes this time it does exist since it's a global table
I have also added this to our wiki, read it here: Check If Temporary Table Exists
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.