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.

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
  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
delete-all-tables-in-sql-server.png

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
delete-all-tables-in-sql-server-2012.png

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
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
  1. declare @command varchar(15)  
  2. set @command = 'drop table ?'  
  3. 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.