Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

Check If Temporary Table Exists

From Wiki

Jump to: navigation, search

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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