Hello! In this article I share a community tool I put together to drop all of the #temporary tables for the current SQL Server session. Have feedback or questions about the content? Feel free to reach out to me at wes@switcharch.com or @wesatswitcharch.
It’s no secret that the only T-SQL development pattern I use more than stored procedures is #temporary tables inside of stored procedures (ha!). Paul White said it best in this blog post – smaller result sets often give the optimizer a better chance of producing a good query plan.
The Problem
The drawback to using temporary tables in stored procedures is that when you start taking code out of the stored procedure to debug it you will find yourself crafting the following statement over and over again:
IF OBJECT_ID('[tempdb].[dbo].[#Company]') IS NOT NULL
DROP TABLE [#Company];
IF OBJECT_ID('[tempdb].[dbo].[#Product]') IS NOT NULL
DROP TABLE [#Product];
IF OBJECT_ID('[tempdb].[dbo].[#ResultSet]') IS NOT NULL
DROP TABLE [#ResultSet];
Building a query to drop your temporary tables isn’t as easy as you would think. Why?
First, the [sys].[tables] DMV in the [tempdb] database will return a record for every temporary table on the instance – even tables for other sessions.
In addition, SQL Server will add trailing underscores and numeric values to the table name to give it uniqueness. As a result, a temporary table you named #ResultSet may appear in the DMVs with the name #ResultSet__________________________________________________________________________________________________________000000327594 (fun fact: this behavior is why temporary tables are limited to 116 characters in length instead of the usual 128 characters).
The Solution
Almost a decade ago (wow…) I put together a utility procedure to identify and drop all of the #temporary tables for the current session using a single command. Today I cleaned it up with the goal of sharing it with the community as [usp_Utility_DropTempTables]. The procedure will drop all of the temporary tables for the current session leaving you with a blank slate.
Get The Tool
You can download the latest version of the [usp_Utility_DropTempTables] stored procedure from Switch Architecture’s community page or directly from our GitHub community repository. Feel free to use the tool, deploy it, and change it as you see best.
Quick Links
View the source code
Download the .SQL file
Using The Tool
Here are some example usage scenarios:
Simple Execution
Drop all temporary tables without any output.
EXEC [dbo].[usp_Utility_DropTempTables];
Execute with Verbose Output
Drop all temporary tables, but list the names of each table for sanity.
EXEC [dbo].[usp_Utility_DropTempTables] @VerboseOutput = 1;
Sample Output:
Generate DROP Commands Only
Build DROP commands for all temporary tables, but do not execute the commands. Simply output the commands to the user for manual use.
EXEC [dbo].[usp_Utility_DropTempTables] @PrintSQL = 1 ,@ExecuteSQL = 0;
Sample Output:
Wes