SQL Server

How To Drop Multiple Tables in SQL Server

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.


2 thoughts on “How To Drop Multiple Tables in SQL Server

  1. Dear Vikas,

    What a thinking!
    I remember a quote from albert einstein on seeing your solution

    Logic will get you from A to B. Imagination will take you everywhere.
    ~Albert Einstein

Leave a Reply

Your email address will not be published. Required fields are marked *