sql - Drop all temporary tables for an instance

Question

I was wondering how / if it's possible to have a query which drops all temporary tables?

I've been trying to work something out using the tempdb.sys.tables, but am struggling to format the name column to make it something that can then be dropped - another factor making things a bit trickier is that often the temp table names contain a '_' which means doing a replace becomes a bit more fiddly (for me at least!)

Is there anything I can use that will drop all temp tables (local or global) without having to drop them all individually on a named basis?

Thanks!

Answer 1

The point of temporary tables is that they are.. temporary. As soon as they go out of scope

• #temp create in stored proc : stored proc exits
• #temp created in session : session disconnects
• ##temp : session that created it disconnects

The query disappears. If you find that you need to remove temporary tables manually, you need to revisit how you are using them.

For the global ones, this will generate and execute the statement to drop them all.

declare @sql nvarchar(max)
select @sql = isnull(@sql+';', '') + 'drop table ' + quotename(name)
from tempdb..sysobjects
where name like '##%'
exec (@sql)

It is a bad idea to drop other sessions' [global] temp tables though.

For the local (to this session) temp tables, just disconnect and reconnect again.