Binaryworld - How to drop all temp tables created by current connection. ... [ SQL Server -> Stored Procedure ]

Few days ago I came up with one problem. I was tesing a script in query analyzer. This script was using several temp tables so every time I run this script I had to make sure that all temp tables are dropped before I run the script otherwise it will give bunch of errors. So I decided to write a procedure to drop all temp tables created by your current session.

you can call the following procedure before running your T-SQL script. If you are running stored procedure then all temp tables are automatically deleted when execution is complete but for batch script temp tables remain in tempdb until you close the connection (i.e. close the Query Analyzer Window).

Click here to copy the following block

ALTER proc usp_DropAllTemp
@DropGlobal bit=0 --Default dont drop global temp table
AS

DECLARE @DROP_STATEMENT nvarchar(1000)
DECLARE cursorDEL CURSOR FOR
SELECT 'DROP TABLE '
+ case
when name like '##%' then name
when name like '#%' then SUBSTRING(name, 1, CHARINDEX( '____', name)-1)
  end as DropSQL
from tempdb..sysobjects
WHERE name LIKE '#%'
AND OBJECT_ID('tempdb..' + name) IS NOT NULL
AND name not like case
when @DropGlobal=0 then '##%' --//Exclude global temp
else '#######%'   --//some fack expression so we can
--//select global temp for delete
  end

--//eventhough we have selected all records from sysobjects
--//but one can access only temp table created by same connection
--//executing this procedure

OPEN cursorDEL
FETCH NEXT FROM cursorDEL INTO @DROP_STATEMENT
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DROP_STATEMENT)
print @DROP_STATEMENT
FETCH NEXT FROM cursorDEL INTO @DROP_STATEMENT
END
CLOSE cursorDEL
DEALLOCATE cursorDEL

GO