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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.