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)