How to delete all tables from the MS SQL database with many foreign key constraints
Sometimes we may have to delete the entire table in the database especially when you want an empty database to use it for another application.
I have been trying to create a new empty database using the existing one. So I thought I will delete all the tables. When I started deleting the tables I realized that there are many foreign key constraints and it has thrown error.
Here I am going to show you how we can delete the table with foreign key constraints.
First we will disable all the foreign constraints using the below query,
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Once you ran below query successfully just execute the below one to delete the entire tables,
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
Make sure you have DBO rights in the database to execute above queries.