Detecting an existence of local temporary table

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
if @.@.ROWCOUNT > 0
--Table #t1 exists
else
--Table #t1 doesn't exist
But the sp_table stored proc.works only for non-temporary tables and
the result using temporary tables is allways @.@.ROWCOUNT = 0.
Does anyone know, if there exists a solution to detect an existing
temporary table ?
Thanks a lot for any suggestions
LiborIf exists(Select name from sysobjects where name='#t1')
--Table #t1 exists
else
--Table #t1 doesn't exist
Madhivanan|||I'd prefer to use
IF object_id('TableName')IS NOT NULL
PRINT 'Yes'
ELSE
PRINT 'No'
<madhivanan2001@.gmail.com> wrote in message
news:1109142292.789540.177840@.o13g2000cwo.googlegroups.com...
> If exists(Select name from sysobjects where name='#t1')
> --Table #t1 exists
> else
> --Table #t1 doesn't exist
>
> Madhivanan
>|||Temp tables are special. You can't just check against sysobjects, and you ca
n'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
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Libor Forejtnik" <lforejtn@.seznam.cz> wrote in message
news:738o11lltb576hp4tdf345qcibnh156sld@.
4ax.com...
> 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
> if @.@.ROWCOUNT > 0
> --Table #t1 exists
> else
> --Table #t1 doesn't exist
> But the sp_table stored proc.works only for non-temporary tables and
> the result using temporary tables is allways @.@.ROWCOUNT = 0.
> Does anyone know, if there exists a solution to detect an existing
> temporary table ?
> Thanks a lot for any suggestions
> Libor|||On 22 Feb 2005 23:04:52 -0800, madhivanan2001@.gmail.com 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|||On Wed, 23 Feb 2005 08:59:44 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Temp tables are special. You can't just check against sysobjects, and you c
an'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