Database crash in SQL Server using pattern

Question

I have an application that creates a separate database (SQL Server 2008) for each new client, during testing we get many databases called PREFIX.whatever ...

I would like a script that will search all databases starting with PREFIX. and leave them so that we can begin a clean test cycle. Any help is greatly appreciated.

Answer 1

Update:

We ended up extending the answer from Baaju, so I decided to share it. We call the following script from MSBuild and clear all existing databases 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;

Answer 2

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

, . .