How to drop many databases which share a common prefix


I recently used AutoSPInstaller to deploy 2010 into a production farm. It provides a great way to automate and reproduce installations. As a great side benefit, it creates all SharePoint databases with the same prefix. After a few runs in a lab environment, I wanted to clean up old SQL databases. Using SSMS to delete databases is very tedious, however, since it only allows you to do one at a time. I wanted an easy way to remove all databases with a common prefix. A Google search yielded a method, but it needed some tweaking to get it to work for me. Below are the steps I used.

1. Run this query to generate a list of commands. In place of “dev_farm” use the prefix of the databases you want to delete.

SELECT ‘ DROP DATABASE ‘ + ‘[‘ + NAME + ‘]’ FROM sys.sysdatabases where name like ‘dev_farm%’

2. In the results window, right click and Select All


3. Right click and choose Copy


4. Paste into a new query window. At the top add USE [master], GO and GO at the end and execute.
As a reference, note the SSMS delete database action creates this query:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’WIN-SP_AdminContentDB’
GO
USE [master]
GO
/****** Object: Database [WIN-SP_AdminContentDB] Script Date: 07/12/2011 15:55:08 ******/
DROP DATABASE [WIN-SP_AdminContentDB]
GO

It differs in that it removes the backup history of the database first. In my environment, I didn’t have backup history for these databases, so this wasn’t a concern.