Have a procedure with tons of temp tables you don’t want to keep track of?  Or debugging some nasty code with tons of temp tables?

This SQL snippet is quite handy.  It will drop all the temp tables in your current scope.

 declare @sql varchar(5000)
 SELECT
       @sql = isnull(@sql+';', '') + 'drop table ' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1)
 FROM tempdb..sysobjects AS t
 WHERE t.name LIKE '#%[_][_][_]%'
 AND t.id =
   OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1));
-- print @sql
 exec (@sql)
 

Enjoy!