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.

Answer 2

In a stored procedure they are dropped automatically when the execution of the proc completes.

I normally come across the desire for this when I copy code out of a stored procedure to debug part of it and the stored proc does not contain the drop table commands.

Closing and reopening the connection works as stated in the accepted answer. Rather than doing this manually after each execution you can enable SQLCMD mode on the Query menu in SSMS

And then use the :connect command (adjust to your server/instance name)

:connect (local)\SQL2014

create table #foo(x int)

create table #bar(x int)

select *
from #foo

Can be run multiple times without problems. The messages tab shows

Connecting to (local)\SQL2014...

(0 row(s) affected)

Disconnecting connection from (local)\SQL2014...