How to drop all temp tables sql server

Deleting temp tables is a simple job, but when we create tons of them and we would like to delete all without disconnecting our session from Sql Server, that can be a little tricky.


For this example we are going to use the following code for generating some temp tables and use WideWorldImporters database, you can use any database as we are not using data directly.

SELECT * INTO #TMP_0 FROM [WideWorldImporters].[Sales].[Orders];
SELECT * INTO #TMP_1 FROM [WideWorldImporters].[Sales].[Orders];
SELECT * INTO #TMP_2 FROM [WideWorldImporters].[Sales].[Orders];
SELECT * INTO #TMP_3 FROM [WideWorldImporters].[Sales].[Orders];

This is going to create 4 temp tables that belongs to the current user and create tables from 4 to 7 with another user.

you can check the list of temp tables by executing the following query.

 SELECT *
 FROM tempdb..sysobjects AS t
 WHERE name LIKE '#%[_][_][_]%' 

That is going to give us a list of all the temp tables that we have on the database, As you can see temp tables are followed by _____________ characters and by a string that identifies the session.

In case you would like to obtain the list of temp tables for the current user/scope you can use the following query.

SELECT *
 FROM tempdb..sysobjects AS t
 WHERE name LIKE '#%[_][_][_]%' 
  AND t.id = OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1
                       , CHARINDEX('___', t.name)-1));

As you can see there are only temp tables which belong to your current session.

If we want to delete temp tables that belongs to our current user we should execute the following dynamic SQL

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);

This is the query that is going to be generated by the dynamic Sql

 drop table #TMP_0;
 drop table #TMP_1;
 drop table #TMP_2;
 drop table #TMP_3

After executing this query you are going to see the following Message

As you can see temp tables that belonged to your session were deleted.


Leave a Reply

Your email address will not be published. Required fields are marked *