test for temporary tables

rgerald · October 9th, 2009, 12:50 PM

I use a lot of temporary tables in my scripts - tables like #tablename.

When I rerun the script, I get error messages that they already exist.

I have tried doing a

Code:

DROP TABLE #tablename

I then get an error message that they do not exist.

How can I test for the existence of the temporary tables before doing the DROP TABLE command?

All the code I've seen so far only works for permanent tables.

rgerald · October 9th, 2009, 01:05 PM

I just figured it out:

Code:

IF OBJECT_ID (N'tempdb..#tablename', N'U') IS NOT NULL
BEGIN
    DROP TABLE #tablename
END

The "tempdb.." seems to be the key to the answer.

om_prakash · October 9th, 2009, 01:06 PM

You can do something like:

Code:

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

Please check the following URL for more details:

http://weblogs.asp.net/jnadal/archiv...24/438960.aspx
http://sqlservercodebook.blogspot.co...le-exists.html