How to Drop Multiple Table with a Naming Pattern in SQL SERVER, drop multiple tables with cursor in sql server drop multiple table from a database

How to Drop Multiple Table with a Naming Pattern in SQL SERVER

Tomorrow I got an email from my boss to drop all tables starting with naming pattern ‘DUMMY’ in test database. There are thousand of tables with NAMING PATTERN DUMMY in that database. It is very annoying to delete all the tables one by one. To solve this I use CURSOR.

First I have to select all tables names from database with given naming pattern. Then fetching them one by one cursor, then apply the drop statement.

QUERY:

--USE DATABASE--
USE TEST
GO
 
--SELECT NAME OF TABLES STARTING WITH DUMMY--
SELECT NAME FROM SYS.TABLES WHERE name LIKE 'DUMMY%'
 
------------------CURSOR STARTING------------------
 
--TO STORE TABLE NAME--
DECLARE @ID VARCHAR(255)
--TO STORE DROP COMMAND--
DECLARE @DROPCOMMAND VARCHAR(255)
--TO STORE NAMING PATTERN OF TABLE--
DECLARE @TABLENAMINGPATTERN VARCHAR(255)
--SET TABLE NAMING PATTERN--
set @TABLENAMINGPATTERN = 'DUMMY%'
 
DECLARE TABLEDROPCURSOR CURSOR FOR
 
SELECT NAME FROM SYS.TABLES WHERE NAME LIKE @TABLENAMINGPATTERN
 
OPEN TABLEDROPCURSOR
FETCH NEXT FROM TABLEDROPCURSOR INTO @ID
 
WHILE @@FETCH_STATUS=0
BEGIN
SET @DROPCOMMAND = N'DROP TABLE ' + @ID
EXECUTE(@DROPCOMMAND)
FETCH NEXT FROM TABLEDROPCURSOR INTO @ID
END
 
CLOSE TABLEDROPCURSOR
DEALLOCATE TABLEDROPCURSOR
 
------------------CURSOR ENDING------------------

Note:- Before dropping tables from database I strongly recommend you to take full backup of database. By doing so make sure you are not going to loose any data.