Dec 032015
 

I’m already at peace with the fact that I’ll never know all of SQL Server’s secrets, but that doesn’t stop me from being surprised every time I figure out something new. In this case, it’s another “secret” hiding in plain sight (Books Online).

It turns out that the DROP DATABASE statement doesn’t just have to drop one database. BOL shows that multiple databases can be specified when separated with commas. Let’s see it in action.

First, create 4 databases:

CREATE DATABASE DB1;
CREATE DATABASE DB2;
CREATE DATABASE DB3;
CREATE DATABASE DB5;

Now drop them:

DROP DATABASE DB1, DB2, DB3, DB5;

Yep, all gone.

But what if there’s an error? Re-run the CREATE statements above, but now let’s drop 5 databases instead of 4. DB4 doesn’t exist (much like Terminal 4 at Chicago’s O’Hare Airport).

DROP DATABASE DB1, DB2, DB3, DB4, DB5;

The above statement will throw an error that it is unable to drop DB4 because that database doesn’t exist, but the other 4 databases are dropped without incident. The same will happen if a user is connected to one of the databases: that one will remain, but all others will be dropped.

So there you have it: you can drop multiple databases with a single statement in SQL Server. (According to Books Online this does not work in Windows Azure SQL Database.) It’s amazing the things you can learn from reading documentation!

 

  4 Responses to “Dropping Multiple Databases At Once”

  1. I love short cuts, keep up the good work

  2. yep, same is true for dropping tables, procs and other objects as well


    create proc pr1 as select 1
    go

    create proc pr2 as select 1
    go

    drop proc pr1, pr2
    GO

    create table t1(id int)
    go

    create table t2(id int)
    go

    drop table t1, t2

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)