Drop All Temporary Tables for an Instance

Drop all temporary tables for an instance

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.

Deleting Global Temporary Tables (##tempTable) in SQL Server

Local temporary tables are destroyed when you close your connection to SQL Server. There is no need to manually purge them under normal circumstances. If you maintain a persistent connection, or connection pooling, you may want to get in the habit of dropping temporary tables immediately after use.

Global temporary tables, on the other hand, since they are visible to all users in a given database, are destroyed along with the last connection which references them.

Drop temporary tables created by Amazon Redshift

Solved.

The reason this table kept existing is because its session had an error and it didn't close as expected.

The only way I found to remove this table was rebooting the Redshift instance.

Drop temp table if it exists

Temp #Tables are created in tempdb. Try this:

IF OBJECT_ID('tempdb..#lu_sensor_name_19') IS NOT NULL 
BEGIN 
    DROP TABLE #lu_sensor_name_19 
END

CREATE TABLE #lu_sensor_name_19...

SQL Server 2016 added the ability to do the drop in one line:

DROP TABLE IF EXISTS #lu_sensor_name_19 

CREATE TABLE #lu_sensor_name_19...

---

Related Topics