Creating Stored Procedure for Dropping Local Temporary Tables in SQL Server

In this final part of the three-part article series, we will review creating a script, wrap it in a stored procedure, and will use it for deleting all local temp tables and one particular local temporary table.

In previous parts of this series, we have reviewed temporary tables and defined their differences from table variables, and then went on with exploring the basics of SQL Server table variables.

Deleting local temporary tables

To delete all SQL local temporary tables, we need to find their locations first. We can do it with the following script: 

 SELECT [t].[name], [t].[object_id]
 FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
 WHERE [t].[name] LIKE '#[^#]%'
 AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
 AND [t].[type] = 'U';

The output is as follows:

Here we get the list of local temp table names and their IDs.

Since SSMS 18 does not have an in-built debugger, let’s apply dbForge Studio for SQL Server to debug the code. 

Write the following code: 

 CREATE TABLE #MyLocalTempTable1 ([ID] INT);
 CREATE TABLE #MyLocalTempTable2 ([ID] INT);

 DECLARE @tbl_name NVARCHAR(255);
 DECLARE @object_id INT;
 DECLARE @tsql NVARCHAR(2000);

 DECLARE sql_cursor CURSOR LOCAL FOR
 SELECT [t].[name], [t].[object_id]
 FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
 WHERE [t].[name] LIKE '#[^#]%'
 AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
 AND [t].[type] = 'U';
 OPEN sql_cursor;
 FETCH NEXT FROM sql_cursor   
 INTO @tbl_name,
      @object_id;
 WHILE (@@FETCH_STATUS = 0)
 BEGIN
   SET @tsql=N'DROP TABLE '+QUOTENAME(@tbl_name);

   EXEC [sys].[sp_executesql] @tsql;

   FETCH NEXT FROM sql_cursor   
   INTO @tbl_name,
        @object_id;
 END

 CLOSE sql_cursor;
 DEALLOCATE sql_cursor;

We create 2 local temp tables and 3 variables for the following processing and eventual deleting of all local temp tables of the session.

Now, let’s debug the code fragment. Select Debug -> Start from the main menu:

After that, we can sequentially check the code in the left bottom corner of the screen (in the Watches tab) and output the necessary parameter values (@tbl_name, @object_id, @tsql):

This way we can control the solution in each code fragment.

Now, let’s change our code. We’ll add the option to define which SQL local temp table we want to delete:

 CREATE TABLE #MyLocalTempTable1 ([ID] INT);
 CREATE TABLE #MyLocalTempTable2 ([ID] INT);

 DECLARE @table_name NVARCHAR(255);

 SET @table_name='#MyLocalTempTable2';

 DECLARE @tbl_name NVARCHAR(255);
 DECLARE @object_id INT;
 DECLARE @tsql NVARCHAR(2000);

 DECLARE sql_cursor CURSOR LOCAL FOR
 SELECT [t].[name], [t].[object_id]
 FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
 WHERE [t].[name] LIKE '#[^#]%'
 AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
 AND [t].[type] = 'U'
 AND (([t].[object_id] = object_id(N'tempdb..' + @table_name)) OR(@table_name IS NULL));
 OPEN sql_cursor;
 FETCH NEXT FROM sql_cursor   
 INTO @tbl_name,
      @object_id;
 WHILE (@@FETCH_STATUS = 0)
 BEGIN
   SET @tsql=N'DROP TABLE '+QUOTENAME(@tbl_name);

   EXEC [sys].[sp_executesql] @tsql;

   FETCH NEXT FROM sql_cursor   
   INTO @tbl_name,
        @object_id;
 END

 CLOSE sql_cursor;
 DEALLOCATE sql_cursor;
 

We stop immediately after setting the @tsql variable value. After that, we launch debugging: 

It will delete only the local temporary table defined in the code.

Now, let’s wrap our solution for DropSessionLocalTempTables in the stored procedure format in the following way:

 CREATE PROCEDURE [dbo].[DropSessionLocalTempTables]
     @table_name NVARCHAR(255) = NULL
 AS
 BEGIN
     SET NOCOUNT ON;
     DECLARE @tbl_name NVARCHAR(255);
     DECLARE @object_id INT;
     DECLARE @tsql NVARCHAR(2000);
     DECLARE sql_cursor CURSOR LOCAL FOR
     SELECT [t].[name], [t].[object_id]
     FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
     WHERE [t].[name] LIKE '#[^#]%'
     AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
     AND [t].[type] = 'U'
     AND (([t].[object_id] = object_id(N'tempdb..' + @table_name)) OR(@table_name IS NULL));
     OPEN sql_cursor;
     FETCH NEXT FROM sql_cursor   
     INTO @tbl_name,
          @object_id;
     WHILE (@@FETCH_STATUS = 0)
     BEGIN
       SET @tsql=N'DROP TABLE '+QUOTENAME(@tbl_name);
       EXEC [sys].[sp_executesql] @tsql;
       FETCH NEXT FROM sql_cursor   
       INTO @tbl_name,
            @object_id;
     END
     CLOSE sql_cursor;
     DEALLOCATE sql_cursor;
 END
 GO

Then we execute the following code:

 CREATE TABLE #MyLocalTempTable1 ([ID] INT);
 CREATE TABLE #MyLocalTempTable2 ([ID] INT);

 SELECT [t].[name], [t].[object_id]
 FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
 WHERE [t].[name] LIKE '#[^#]%'
 AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
 AND [t].[type] = 'U';

 EXEC [dbo].[DropSessionLocalTempTables];

 SELECT [t].[name], [t].[object_id]
 FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
 WHERE [t].[name] LIKE '#[^#]%'
 AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
 AND [t].[type] = 'U';

The results show that all the local temporary tables have been deleted. It is so because we haven’t set the @table_name input parameter:

However, if we set the @table_name parameter, it will delete this particular table only:

 CREATE TABLE #MyLocalTempTable1 ([ID] INT);
 CREATE TABLE #MyLocalTempTable2 ([ID] INT);

 SELECT [t].[name], [t].[object_id]
 FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
 WHERE [t].[name] LIKE '#[^#]%'
 AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
 AND [t].[type] = 'U';

 EXEC [dbo].[DropSessionLocalTempTables] @table_name=N'#MyLocalTempTable2';

 SELECT [t].[name], [t].[object_id]
 FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
 WHERE [t].[name] LIKE '#[^#]%'
 AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
 AND [t].[type] = 'U';

In our case, it deleted one particular local temporary table.

Note: To format the code, we used the SQL Complete tool.

The stored procedure for deleting the local temporary tables has been implemented as [srv].[DropSessionLocalTempTables] in the SRV database.

Conclusion

We have reviewed and compared local and global SQL server temporary tables, as well as table variables. Additionally, we created a script, wrapped it in a stored procedure, and used it for deleting all local temporary tables or one particular local temporary table. We hope you will find all of these insights helpful.

References

1. SQL Server Documentation
2. Hints
3. SSMS
4. Debugger for SSMS
5. dbForge Studio for SQL Server
6. SQL Tools
7. SQL Debugger in dbForge Studio for SQL Server