SQL 2005 - check if Temp Table exists

[wxman]

SQL2005 SP2

Suggections on how I check if a temp table (#messages_temp) exists in a database?

something like...
if exists(#messages_temp)
BEGIN
DROP #messages_temp
END
ELSE
BEGIN
CREATE #messages_temp
END

 

[hyperD]

Quick reply, not checked - hope it helps

USE PUBS
GO

CREATE TABLE #some_temp_name(id INT)
GO

USE TEMPDB
GO

SELECT OBJECT_NAME(OBJECT_ID('#some_temp_name'))

USE PUBS
GO


IF OBJECT_ID('tempdb..#some_temp_name') IS NOT NULL
PRINT '#some_temp_name exists.'
ELSE
PRINT '#some_temp_name does not exist.'

 

[Spacecadet]

spooky

I was experimenting around and wrote this bit of SQL not more than an hour ago:

select * into #temp1 
from  [add your source objects here]
where 1=2 

select * from tempdb.dbo.syscolumns
where id = (select id from tempdb.dbo.sysobjects where name like '#temp1%')

drop table #temp1

 

[Sir_Edward_Matheson]

if object_id('tempdb..#temptbl', 'u') is not null
...
@Spacecadet
You should be using information_schema.tables and not sysobjects

 

[Spacecadet]

@Spacecadet
You should be using information_schema.tables and not sysobjects

you are right, i was just messing around with something though

 

[wxman]

Great I have got it workling - it was actually the double dot .. between the tempDB and the table name that was scuppering me

Thank's to all that suggest fixes