Check if temporary table exists

Hello.

How can I check if a temporary table exists in the current context?

With normal tables I'd do a

EXISTS ( SELECT name FROM sysobjects

WHERE name='myTableName' AND type='U')

However, I can't do that with a temporary table. I'd have to go look at the sysobjects table in the tempdb database.

The problem is that for temporary tables, a suffix is added to the name to make it unique for each scope. I can't change the WHERE clause to name LIKE 'myTableName%' because this would return true if a temporary table with the same name exists in a different scope.

Any ideas?

Carlos

You can try the following

IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END

CREATE TABLE #MyTempTable
(
ID int IDENTITY(1,1),
SomeValue varchar(100)
)
GO

|||IF OBJECT_ID('tempdb..#MyTempTable', 'U') IS NOT NULL
Print 'Yes'
Else
Print 'No'sql

Check for Temp Table

How do you check if a temp table exists?
I usually do the following for tables and Views:
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME =
'EarningsDeductions')
DROP Table EarningsDeductions
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME =
'EarningsWithRank')
DROP VIEW EarningsWithRank
But I can't seem to find out how to check for a temp Table.
I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
was there and whether there was a type code there, but there wasn't.
Thanks,
Tomtry this:
http://www.devx.com/tips/Tip/13938
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
> How do you check if a temp table exists?
> I usually do the following for tables and Views:
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME
> = 'EarningsDeductions')
> DROP Table EarningsDeductions
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME
> = 'EarningsWithRank')
> DROP VIEW EarningsWithRank
> But I can't seem to find out how to check for a temp Table.
> I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
> was there and whether there was a type code there, but there wasn't.
> Thanks,
> Tom
>
>|||or this :
if object_id('tempdb..#temp') is not null
print 'exists'
else
print 'not exists'
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
> How do you check if a temp table exists?
> I usually do the following for tables and Views:
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME
> = 'EarningsDeductions')
> DROP Table EarningsDeductions
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME
> = 'EarningsWithRank')
> DROP VIEW EarningsWithRank
> But I can't seem to find out how to check for a temp Table.
> I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
> was there and whether there was a type code there, but there wasn't.
> Thanks,
> Tom
>
>|||"Yosh" <yoshi@.nospam.com> wrote in message
news:%23%23mEoyxvFHA.708@.TK2MSFTNGP10.phx.gbl...
> or this :
> if object_id('tempdb..#temp') is not null
> print 'exists'
> else
> print 'not exists'
That would do what I wanted.
Thanks,
Tom
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
NAME
NAME
it
>