How to safely drop all tables in a MS SQL database

Some time ago, for testing purposes, I needed a fast and simple way to drop all tables from a database. If possible it should not cause any collateral damage and be easy to use. How hard can it be? Right?

I recommend reading the whole entry instead of pasting the provided queries willy-nilly. You don’t want to be *that guy* who broke an important database.

Undocumented solution

Short googling resulted in a StackOverflow page which, among others, had the following query.

Easy, simple and working solution. Later I shared it with our DB developer but she pointed out that I used undocumented stored procedure. After that she started using terms I wasn’t aware of. Another problem was that I had to keep an eye on a SSMS connection and make sure that I was querying right database. Also it didn’t work on Azure hosted databases.

Fancy solution

A professional DB developer may call above query a nifty piece of code. Unfortunately I still had to keep an eye on SSMS connection. Also people with basic SQL knowledge may find this query too complex for their taste.

Mistakeproof solution

Third solution proved to be a bit slower but it reduced the chance of unwanted accidents. You have to navigate to proper server and database and show which tables you want removed.

With SSMS open press F7 or select View and Object Explorer Details. Doing so will bring, well… Object Explorer Details window.

Then navigate to a desired database and open Tables folder which will contain all tables in a given DB. After that select tables you want removed, right click and pick Delete from the context menu.

This will bring up a summary screen. Leave the options as they are and click OK buttons couple of times (depending on a number of FK references).

Example of Get-Command syntax and a sample output

Related Posts

2 COMMENTS

  1. August 13, 2018 16:43 Reply

    Worked like a charm, thanks.

  2. Thomas Tofft Williams
    August 23, 2018 18:01 Reply

    Awesome! Thank you for the gem, very useful

Leave a reply