Drop all temp tables(local and global) of a session in SQL Server.

Drop all temp tables(local and global) of a session in SQL Server.

Below code is helpful wherein you are not aware of the temp tables created in your current session.
When we debug the procedure,if that procedure doesn't have construct of DROP and CREATE.



DECLARE @DropGlobal bit=0 --Default dont drop global temp table
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