Introduction
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
- create database Daljeet
- go
- use Daljeet
First of all we create three tables in a database.
Creation of the first table
- create table emp(empId int, empName varchar(15))
- go
- insert into emp
- select 1,'Deepak'union all
- select 2,'Arora'
- go
- select * from emp
![delete-all-tables-in-sql-server.png]()
Creation of the second table
- create table stu(stuId int, stuName varchar(15))
- go
- insert into stu
- select 11,'Daljeet'union all
- select 22,'Singh'
- go
- select * from stu
![delete-all-tables-in-sql-server-2012.png]()
Creation of the third table
- create table prod(stuId int, stuName varchar(15))
- go
- insert into prod
- select 101,'Mobile'union all
- select 202,'laptop'
- go
- select * from prod
![delete-all-tables-in-sql-serverr.png]()
Sp_msforeachtable
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
- declare @command varchar(15)
- set @command = 'drop table ?'
- exec sp_msforeachtable @command
![delete-all-tables-in-sql-server-.png]()
Summary
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.