In this post i will discuss how To Drop Multiple Tables in SQL Server starting with a fixed pattern
Situation: I want to drop multiple tables which starts with a name “temp_”. Suppose I have 30 tables in my database which I need to drop and I don’t want to write drop statement 30 times. Instead I will use T-SQL script to drop multiple tables.
Here I use sys.tables command to find the names of the tables with a filter condition to list all the tables which starts with “temp_” as shown in the following figure.
Here temp_A,temp_B,temp_C,temp_D and temp_E are the tables in my database which I used for demonstration.
Now i will rewrite the command by concatenating ‘drop name ’ string as shown in the following figure.
Now Right Click on the column as shown in figure above ,copy the column ,paste it in SSMS, generate the script and execute it.
drop table temp_A
drop table temp_B
drop table temp_C
drop table temp_D
drop table temp_E
This will help to save time when you have a lot of tables to delete because now you don’t need to write these scripts by typing the drop command for all the tables which you want to delete
Important Note: Please review your script before running the delete command because the script generated accidently may have any table which you don’t want to delete or make sure you have the backup before running the script.