How To Drop Multiple Databases In SQL Server

During development, Developer generally create a bunch of temporary databases that you probably do not want to keep in the database for long term. Most DBAs drop at a time using either a single DROP statement or SSMS. This post explains how we can drop multiple databases in a single query.

To drop multiple database we can write it as:

1
DROP DATABASE DB1, DB2, DB3

 
We can see that we just need to put the list of databases to drop and separate them with a comma.

NOTE : If some databases in the list do not exist or can not be dropped due to privileges or they do not exist, the remaining objects will be successfully dropped without any negative impact.

 
In the above way, we need to manually specify the databases names to drop them. This can be simplified using some pattern if names follows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE master
GO
DECLARE @dbnames nvarchar(MAX)
DECLARE @statement nvarchar(MAX)
SET @dbnames = ''
SET @statement = ''
SELECT @dbnames = @dbnames + ',[' + name + ']' FROM sys.databases WHERE name LIKE 'Sample%'
IF len(@dbnames) = 0
    BEGIN
    print 'no databases to drop'
    END
ELSE
    BEGIN
    SET @statement = 'drop database ' + SUBSTRING(@dbnames, 2, len(@dbnames))
    EXEC sp_executesql @statement
    END

If you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.

Happy Learning 🙂

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.