SQLTeam.com | Weblogs | Forums

Is there a command to drop all temp tables when re-running the query?


When I re-run a query, it says that the temp table objects already exist so I have to open a new window in SSMS.

Is there a command to clear all temp tables after the query ends or before it begins without having to drop one by one?

I use T-SQL


Yes - you can put code in to drop the tables if they exist:

Drop Table If Exists #myTempTable;

This answer that was already given to you by @robert_volk should help guide you. Also read the documentation of temp tables

This generates the list for the current session:

SELECT 'DROP TABLE ' + LEFT([name], CHARINDEX('_', [name]) -1) + ';'
FROM tempdb.sys.objects
WHERE [name] LIKE '#%'
	AND CHARINDEX('_', [name]) > 0
	AND [type] = 'U'
	AND NOT object_id('tempdb..' + [name]) IS NULL;

please see this link which shows WHAT to do

1 Like