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

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

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

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

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.