When working with SQL data and lots of foreign keys, you may have the need to drop everything. Well I have just the right SQL statement for you. The following statement will drop everything the given database, including the following:
- non-system stored procedures
- views
- functions
- foreign key constraints
- primary key constraints
- tables
You can execute parts of the script on its own if you only want to drop a certain part.
Drop everything in a MSSQL database
The SQL script must be execute on the database level. Optionally you can add an USE ‘YOUR_DATABASE_NAME’ statement on the top.
Update 07.01.2019
I added the option to iterate over all schemas in the selected database and drop everything in every schema including the schema.
2 Comments
Fernando Gómez · 22. December 2018 at 22:40
This is awesome, thanks. It worked like charm on most of my databses, however I noticed that the [dbo] schema is hardcoded, how could it work when a database has several schemas (other than changing the [dbo] string)?
Michael · 7. January 2019 at 11:02
I added a statement to drop all schemas in a database afterwards, maybe that would do it?
As an alternative you could take the last part I just added and modify the script to iterate over all schemas and execute the drops accordingly.I added a second gist that iterates over all schemas in the database and executes the drop all statements for every schema. I did not test it, but maybe that should do it for you.