Detecting an existence of local temporary table

Temp tables are special. You can't just check against sysobjects, and you can't just check the
object id locally (as they live in tempdb). Try below:

IF OBJECT_ID('tempdb..#t') IS NOT NULL
PRINT 'exists'
ELSE
PRINT 'not exists'


--
Tibor Karaszi, SQL Server MVP
<a rel="nofollow" style='text-decoration: none;' href="http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/sqlserver/default.asp</a>
<a rel="nofollow" style='text-decoration: none;' href="http://www.solidqualitylearning.com/" target="_blank">http://www.solidqualitylearning.com/</a>


"Libor Forejtnik" wrote in message

 > Hi,
 >
 > I create a temporary table. for example:
 >
 > create table #t1 (ID_T1 integer)
 >
 > Next I would like to detect If the table already exists:
 >
 > exec sp_table #t1

 > --Table #t1 exists
 > else
 > --Table #t1 doesn't exist
 >
 > But the sp_table stored proc.works only for non-temporary tables and

 >
 > Does anyone know, if there exists a solution to detect an existing
 > temporary table ?
 >
 > Thanks a lot for any suggestions
 >
 > Libor
 >> Stay informed about: Detecting an existence of local temporary table 

I'd prefer to use
IF object_id('TableName')IS NOT NULL
PRINT 'Yes'
ELSE
PRINT 'No'




wrote in message

 >
 > If exists(Select name from sysobjects where name='#t1')
 > --Table #t1 exists
 > else
 > --Table #t1 doesn't exist
 >
 >
 > Madhivanan
 >
 >> Stay informed about: Detecting an existence of local temporary table 

On 22 Feb 2005 23:04:52 -0800, wrote:

 >
 >If exists(Select name from sysobjects where name='#t1')
 > --Table #t1 exists
 >else
 > --Table #t1 doesn't exist
 >
 >
 >Madhivanan


Yes, the query should be exactlyt:

if exists(select * from tempdb..sysobjects where name like '#t1%')
..
..

but such query cannot be used for this purpose: while one session
creates a #t1 table and then other session performs such query
without creating is's own #t1 table, the result is allways more
than 0 rows.

Somewhere on server resides a relationship between a record of
tempdb..sysobject with name like '#t1%' and the user session. I tried
to recover such relationship, but (so far) I was unsuccessfull.

Libor
 >> Stay informed about: Detecting an existence of local temporary table 

On Wed, 23 Feb 2005 08:59:44 +0100, "Tibor Karaszi"
wrote:

 >Temp tables are special. You can't just check against sysobjects, and you can't just check the
 >object id locally (as they live in tempdb). Try below:
 >
 >IF OBJECT_ID('tempdb..#t') IS NOT NULL
 >PRINT 'exists'
 >ELSE
 >PRINT 'not exists'

This works very good. Thanks

Libor
 >> Stay informed about: Detecting an existence of local temporary table