There a way to check if Temp Table exists? 1

[nerdalert1]

I want to check if a #temp table exists at the start of a proc and then drop it if it does. Anyone know? Thanks all.

 

[mrdenny]

Yes there is. You have to check the sysobjects table in the tempdb database.

use master
go
if exists (select * from tempdb.dbo.sysobjects where name like '#test%')
    drop table #test
create table #test
(c1 int)
go

Now this doesn't account for more than one spid having a temp table with the same name. But I think you get the idea.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLSister]

A local #temp table cannot exist at the start of a stored procedure calling it. IT isn;t in scope until you creat it within the stored porcedure and each user running the procedure creates their own version. You only need to check for global temp tables (those starting with ##).

Of course table variables are generally more efficient, although they can do everything a temp table can.

Questions about posting. See faq183-874

 

[nerdalert1]

SQLSister thats right. Thanks for the info. I forgot that.

 

[nerdalert1]

SQLSister but variables stay in memory worst dont they?