SQL SERVER – DROP Multiple Temp Tables Using Cursors on Azure

4 years ago

Today’s blog post is written based on the comment of SQL Server Expert Ahliana Byrd on the blog post SQL SERVER – How to DROP Multiple Tables Using Single DROP Statement? I request you to read the original blog post to get the context of this why the cursor is used in this case. Please note, the original blog post’s solution does not have cursor but as explained in the following paragraph Ahliana, they need this cursor to drop temp tables on Azure.

While working on Azure Ahliana has to create many temporary stored procedures and temporary tables (NOT temp tables or #tables). They are essentially creating programming pieces for their overall stored procedure to use, such as logging or other specialized pieces. To identify those objects, they are prefixed with _Temp_ (underscoreTempunderscore). Once the temp objects serve the purpose they needed to be searched, identified and destroyed. It would not be possible to identify each of them manually, hence the automated script with the help of cursor.

DROP Multiple Temp Tables

Here is the script which will go through the entire database and find tables with a specific prefix and drop them. In our case the prefix is _Temp_.

DECLARE @Name NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
-- Declare Cursor 
DECLARE curTables CURSOR FOR
	SELECT NAME
	FROM SYS.OBJECTS
	WHERE NAME LIKE '_TEMP_%'
	AND TYPE = 'u'
-- Open Cursor
OPEN curTables
FETCH NEXT FROM curTables INTO @Name
	WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @SQL = 'DROP TABLE dbo.' + QUOTENAME(@Name)
			PRINT @SQL
			EXEC sp_executesql @SQL
			FETCH NEXT FROM curTables INTO @Name
		END
	CLOSE curTables
DEALLOCATE curTables
GO

To test the above code you can create few of the tables with the prefix _Temp_ and execute the above code and you will notice those will be removed once the execution completes.

Here is the code to create a few test tables:

CREATE TABLE [dbo].[_Temp_](
[ID] [nchar](10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[_Temp_1](
[ID] [nchar](10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[_Temp_2](
[ID] [nchar](10) NULL
) ON [PRIMARY]
GO

If you are creating temporary stored procedures, Ahliana has also provided the code for the same, which you can refer here.

If you have written any such interesting script, please send me a direct email at my email address and I will be happy to post on the blog with due credit to you. You can also leave a comment in this blog post as well.

Reference: Pinal Dave (https://blog.SQLAuthority.com)