Dropping All the Tables in Database in SQL Server 2012


In this article I describe how to drop all the tables in a particular database. Several times in our project we needed to drop all tables. If there is a large number of tables then it is very tedious to drop all of them. Here I explain how to drop all the tables.

Creation of database
  1. create database Daljeet  
  2. go  
  3. use Daljeet  
First of all we create three tables in a database.

Creation of the first table
  1. create table emp(empId int, empName varchar(15))  
  2. go  
  3. insert into emp  
  4. select 1,'Deepak'union all  
  5. select 2,'Arora'  
  6. go  
  7. select * from emp

Creation of the second table
  1. create table stu(stuId int, stuName varchar(15))  
  2. go  
  3. insert into stu  
  4. select 11,'Daljeet'union all  
  5. select 22,'Singh'  
  6. go  
  7. select * from stu

Creation of the third table
  1. create table prod(stuId int, stuName varchar(15))  
  2. go  
  3. insert into prod  
  4. select 101,'Mobile'union all  
  5. select 202,'laptop'  
  6. go  
  7. select * from prod


It is a Stored Procedure that is used to run a query on each table in the database. Sp_foreachtable is an undocumented Stored Procedure that is not listed in MSDN books. When we run a query using Sp_msforeachtable then we use ? in the place of the table name.

Query to remove all the tables
  1. declare @command varchar(15)  
  2. set @command = 'drop table ?'  
  3. exec sp_msforeachtable @command


In this article, I described how to drop all tables in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.

Similar Articles