Check for existence of temporary table

Another of those tricks that isn't well documented is the ability to check for the existence of a temporary table.

Many places say it's not possible for #tables, well they're wrong. object_Id will return a non null value if you use the following format tempdb..#temptable combined with object exists, i.e.

if object_id('tempdb..#temptable) is null
--object doesn't exist

enjoy


-
Published Wednesday, November 9, 2005 8:03 AM by simonsabin
Filed under:

Comments

Friday, October 26, 2007 1:07 PM by Chrs Brooksbank

# re: Check for existence of temporary table

Thanks a lot for this, everything else I looked at suggested this wasnt possible.

Friday, January 4, 2008 3:11 PM by Paul Berry

# re: Check for existence of temporary table

Little gems like this put the Microsoft documentation to shame. Thanks for this one!

Tuesday, March 18, 2008 3:11 PM by Sena Munasinghe

# re: Check for existence of temporary table

Thanks, This solved my problem

Wednesday, March 19, 2008 1:36 PM by Devin

# re: Check for existence of temporary table

Great! It works!

Thank you!

Wednesday, September 2, 2009 3:46 PM by SimonS Blog on SQL Server Stuff

# Handling Temp tables in reporting services

Today in my 24Hrs of PASS session I highlighted the issue of using temp tables with reporting services. My solution is to use a design time variable, and the feature that all paths are executed when FMTONLY is set ON The code I used is along these lines