Solved

drop all the tables in a database

Posted on 2016-07-15
5
47 Views
Last Modified: 2016-07-18
I need a script to drop all the tables in a database. What would be the easiest way to do that?
0
Comment
Question by:vbnetcoder
5 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
wouldn't it be easier to drop the database and recreate it?
1
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
or you can try:
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

Open in new window

0
 

Assisted Solution

by:vbnetcoder
vbnetcoder earned 0 total points
Comment Utility
Eric you might be right on dropping the database
0
 
LVL 7

Accepted Solution

by:
Jason clark earned 500 total points
Comment Utility
Try below script to drop all tables:

SELECT ' Drop table ' + s.NAME + '.' + t.NAME
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U' 

Exec sp_executesql @sql

Open in new window


But remember If you have any foreign Keys defined between tables then first run the below query to disable all foreign keys present in your DB.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Open in new window

0
 
LVL 8

Expert Comment

by:Jose Torres
Comment Utility
Remember by dropping the database you loose all objects (views, stored procedures.....)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL 2012 and SQL 2014 always on 9 23
C# SQL BULK INSERT CLASS 5 34
Distinct values from two tables 14 17
separate column 24 19
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now