Dropping all Temp tables

I wanted to drop all my temp tables at the top of a large SELECT statement and already had a query to drop a Named temp-table …

IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL DROP TABLE #Temp2

… so just needed to make that dynamic. Looking at the temp tables …

select * from tempdb.sys.objects where name like '#%'

… I noticed all mine had a bunch of underscores and a GUID appended to the name. And the GUID always contained atleast 5 zero’s. So I was able to filter out just mine with

select name from tempdb.sys.objects where name like '#000%'

… and paste that into a while loop to drop them …

--first drop all temp tables
IF OBJECT_ID('temptables') IS NOT NULL DROP TABLE temptables
select name into temptables from tempdb.sys.objects where name like '#000%'

declare @table varchar(max), @cmd varchar(max)
while (select count(*) from temptables) > 0
begin
 set @table = (select top 1 name from temptables)
 set @cmd = 'IF OBJECT_ID(''tempdb..' + @table + ''') IS NOT NULL DROP TABLE ' + @table
 exec(@cmd)
 delete from temptables where name = @table
end

DROP TABLE temptables

By initially creating a list and then working through it deleting the temp-table and its entry in the list, I ensured there would be no endless looping should it try (and fail) to drop a temp-table I don’t have permissions on.