Drop multiple table with a naming pattern in sql server

Yesterday I got an email from my boss to drop all tables starting with naming pattern ‘DUMMY’ in test database. There are thousands of tables with naming pattern DUMMY in that database. It is very tedious task to delete all the tables’ one by one.

  

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

With the help of below sql script we can achieve the same

--Get the list DataBase name DECLARE @tmpdatabase table (ID int IDENTITY (1,1), tablename varchar(100)) --Insert table name in variable table Insert into @tmpdatabase SELECT NAME FROM SYS.TABLES WHERE name LIKE 'DUMMY%' DECLARE @MinID int=1, @MaxID int = (select Count(*) from @tmpdatabase) while (@MinID<=@MaxID) BEGIN declare @tName varchar(100) select @tName=tablename from @tmpdatabase where ID=@MinID DECLARE @s varchar(100) set @s= N'DROP TABLE ' + @tName exec (@s) set @MinID=@MinID+1 End

See the result

Now I am seeing it on the Test database

  

All tables which started with DUMMY is dropped.

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 lose any data.