Drop database in SQL Server using wildcard

Question

I have an application that creates a separate database (SQL Server 2008) for each new customer, during testing we end up with a lot of databases called PREFIX.whatever ...

I would love a script that would look for all databases starting with PREFIX. and drop them so we can start a clean test cycle. Any help greatly appreciated.

Answer 1

SELECT ' DROP DATABASE ' + NAME FROM sys.sysdatabases where name like 'PREFIX%'

Copy the output and execute this to drop DBs in your criteria. You can also schedule this on a daily basis with a little tweaking.

Answer 2

Update:

We ended up expanding the answer from Baaju so I thought I would share it. We call teh following script from MSBuild and it cleans out all of teh existing DB's created during testing:

use master

DECLARE @Name nvarchar(1000);

DECLARE testdb_cursor CURSOR FOR
SELECT 'ALTER DATABASE' + '[' + NAME + ']' + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE    DROP DATABASE ' + '[' + NAME + ']' FROM sys.sysdatabases where name like 'TCM.%'

OPEN testdb_cursor;

-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM testdb_cursor
INTO @Name;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   -- Concatenate and display the current values in the variables.
   exec sp_executesql @Name;

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM testdb_cursor
   INTO @Name;
   END

CLOSE testdb_cursor;
DEALLOCATE testdb_cursor;