Drop all temporary tables for an instance

The version below avoids all of the hassles of dealing with the '_'s. I just wanted to get rid of non-global temp tables, hence the '#[^#]%' in my WHERE clause, drop the [^#] if you want to drop global temp tables as well, or use a '##%' if you only want to drop global temp tables.

The DROP statement seems happy to take the full name with the '_', etc., so we don't need to manipulate and edit these. The OBJECT_ID(...) NOT NULL allows me to avoid tables that were not created by my session, presumably since these tables should not be 'visible' to me, they come back with NULL from this call. The QUOTENAME is needed to make sure the name is correctly quoted / escaped. If you have no temp tables, @d_sql will be the empty string still, so we check for that before printing / executing.

DECLARE @d_sql NVARCHAR(MAX)
SET @d_sql = ''

SELECT @d_sql = @d_sql + 'DROP TABLE ' + QUOTENAME(name) + ';
'
FROM tempdb..sysobjects
WHERE name like '#[^#]%'
AND OBJECT_ID('tempdb..'+QUOTENAME(name)) IS NOT NULL

IF @d_sql <> ''
BEGIN
    PRINT @d_sql
    -- EXEC( @d_sql )
END

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.

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...