Schema Z – Remove Temp Tables

Local temporary tables are the unsung heroes of SQL programming — created in a flash, used extensively, and then often… left behind like forgotten leftovers in the fridge. Imagine a long script that creates temporary tables and references them throughout. Once executed, if you need to run it again in the same session, you’ll first have to remove those pesky temp tables before recreating them.

Most people write cleanup code at the beginning or end of their scripts, like this classic approach:

if object_id('tempdb..#MyTemp') is not null
	drop table #MyTemp

This works – but who has time to babysit temp tables when Schema z can handle it for you?

With procedure z.usp_DropAllLocalTempTables, you can effortlessly remove all local temporary tables in one go. No more hunting them down manually.

select * into #MyTemp from sys.objects
go
select object_id('tempdb..#MyTemp')
go
exec z.usp_DropAllLocalTempTables
go
select object_id('tempdb..#MyTemp')

/*
(344 rows affected)

-----------
-1544820000

(1 row affected)


-----------
NULL

(1 row affected)
*/

Now you can focus on writing SQL instead of chasing down temp tables.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.