Handling # (temp) table

Hi,

Handling # table is giving problem.
Does anybody have solution/suggestion for the below given problem.

Create a #TMPTAB table
--------
SELECT GETDATE() MYDATE INTO #TMPTAB

Select #TMPTAB Table
------
SELECT NAME FROM TEMPDB..sysobjects where name ='#TMPTAB'
OR
SELECT NAME FROM sysobjects where name ='#TMPTAB'

--------
It does not return the record.
(Because SQL Server create # table with an system generated unique ID
in Name like (#TMPTAB________________________________000000011F 8F))

-----
But if I select it with Like clause:
SELECT NAME FROM TEMPDB..sysobjects where name ='#TMPTAB'
It return the name:
#TMPTAB________________________________000000011F8 F

When I drop the table it canbe drop with
DROP TABLE #TMPTAB

(EVEN IF I'M USING SOME OTHER DATA BASE 'XDATABASE')

NOW THE PROBLEM IS:
====================
IF I CHECK TABLE WITH LIKE CLAUSE IT WILL RETURN ME ALL # TABLE, WHICH
HAS BEEN CREATED IN TEMPDB.. DATABASE BY DIFFERENT USER/CLIENT).

IN A SCENERIO WHERE A PARTICULAR CONNECTION DOES NOT HAVE MADE ANY #
TABLE, BUT IT HAS MADE FROM SOME ANOTHER INSTANCE.

IF I SEARCH TABLE WITH LIKE CLAUSE (SELECT NAME FROM
TEMPDB..sysobjects where name ='#TMPTAB'), IT WILL RETURN TRUE AND
THEN AT THE TIME OF DROPPING TABLE IT WILL RETURN ERROR.

WHY THIS HAPPEN?

DOES ANYBODY HAVE SOME SOLUTION/SUGGETION ON IT.

THANKS IN ADV.

T.S.NEGI
tilak.negi@.mind-infotech.comUse OBJECT_ID to test for the existence of a temp table:

IF OBJECT_ID('tempdb..#tmptab') IS NOT NULL
...

--
David Portas
SQL Server MVP
--