SQL Server  

Dropping All the Tables in Database in SQL Server 2012

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

Output:

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

Output:

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

Output:


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

Output:

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.
  


View All Comments