Home > T-SQL > T-SQL: How to drop all databases in SQL Server

T-SQL: How to drop all databases in SQL Server


Drop all the databases in an instance, easy right? manually deleting each databases is not big deal but what if you have more than 150 databases and you are asked to drop all?

We are not going to do it DB by DB rather we can automate it by creating a script to perform the actions.

I have tried something to implement this using metadata tables. This query below will list up the all the databases in the server from sys.databases and uses a cursor to iterate each db name. The dynamic sql inside the loop, drops the DB one by one.

This query may be useful when you have hundreds of Databases in an instance and you have an sudden requirement to clean up the server. This will be easier than to drop the databases manually.

Note:   Executing the query below will drop all the databases in the server. Carefully check the list of DBs by printing it (PRINT @sql). When you are sure and confirmed about the list of Databases that has to be dropped, go and uncomment the EXEC cmd (EXEC @sql) in script and run the query

Code:

-----------------------------------------------------------------------------------

 DECLARE @sql VARCHAR (max)
 DECLARE @DBname VARCHAR (50)
 DECLARE DBS CURSOR FOR
 SELECT name
 FROM   sys.databases
 WHERE  name NOT IN ( 'model', 'tempdb', 'master', 'model',
 'reportserver', 'ReportServerDB', 'msdb')
 OPEN DBS
 FETCH next FROM DBS INTO @DBname
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @sql = 'DROP DATABASE ' + @DBname
 PRINT @sql
 --exec @sql
 FETCH next FROM DBS INTO @DBname
 END
 CLOSE DBS
 DEALLOCATE DBS

Happy Querying 😉

Advertisements
  1. Bala Krishna
    March 20, 2013 at 4:38 am

    HI Dinesh,

    NIce one.. Thanks for sharing such nice things.

    Like

    • March 21, 2013 at 4:03 pm

      Thanks Bala,

      Saw your post, brilliant use of cursors, Thanks for sharing it on this blog

      Like

  2. Bala Krishna
    March 20, 2013 at 5:09 am

    Previously i also over come same kind of problem regarding tables. please go through the following link.

    http://beyondrelational.com/modules/3/ask/questions/19050/how-to-run-a-command-stored-in-table.aspx

    Like

  3. shmuel k
    September 3, 2015 at 2:32 pm

    10x !!

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: