Wednesday, March 21, 2012

How check if table exists

I have some code in a .sql file that creates a temp table (##temp01)

I also have code that will drop this table. The problem is, the table sometimes exists when this line is run and sometimes it doesn't. How to I check to see if ##Temp already exists before trying to drop it?

If Object_ID('tempdb..##temp01') Is Not Null --Meaning it exists
...

|||

If the table is created inside a stored procedure, it will be dropped when the stored proc ends anyway.

HTH

|||

To demonstrate:

create procedure test_temp
as

create table #temp(id int)

SELECT * FROM #temp

Go

exec test_temp -- selects 0 rows, table exists
SELECT * FROM #temp -- throws error, no table exists

No comments:

Post a Comment