Handling # (temp) table

[T.S.Negi]

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..sysobje cts 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_______ _______________ __________00000 0011F8F))


---------------
But if I select it with Like clause:
SELECT NAME FROM TEMPDB..sysobje cts where name ='#TMPTAB'
It return the name:
#TMPTAB________ _______________ _________000000 011F8F




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..sysobje cts 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.com

[David Portas]

Re: Handling # (temp) table

Use OBJECT_ID to test for the existence of a temp table:

IF OBJECT_ID('temp db..#tmptab') IS NOT NULL
...

--
David Portas
SQL Server MVP
--