Global Temporary table delete operation

Question

How to check if the global Temporary table exists in SQL server, if yes then delete that global temporary table?

I am trying to execute this:

IF OBJECT_ID('##Table', 'U') IS NOT NULL  
  DROP TABLE ##Table

...but it is not working.

Answer 1

look in this View to see if the table exists:

[tempdb].[INFORMATION_SCHEMA].[TABLES]

Answer 2

You can detect temp table presence with

IF OBJECT_ID('tempdb.dbo.##Table', 'U') IS NOT NULL

and, surprisingly to me, you can drop it from any connection with

DROP TABLE ##Table

However, I can't help but think that doing so would be a bad idea, since presumably the connection/user who created it might still be using it...

Answer 3

To check the presence of temp table and delete it

IF OBJECT_ID('tempdb..##Table' , 'U') IS NOT NULL
   drop TABLE ##Table