drop all the tables in a database

I need a script to drop all the tables in a database. What would be the easiest way to do that?
vbnetcoderAsked:
Who is Participating?
 
Jason clarkDBA FreelancerCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
wouldn't it be easier to drop the database and recreate it?
1
 
Éric MoreauSenior .Net ConsultantCommented:
or you can try:
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

Open in new window

0
 
vbnetcoderAuthor Commented:
Eric you might be right on dropping the database
0
 
Jose TorresSenior SQL Server DBACommented:
Remember by dropping the database you loose all objects (views, stored procedures.....)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.