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:
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.