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 Databases 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;

Answer 3

Just ran into this and come up with a slight variation to allow immediate execution without cursors:

DECLARE @SQL NVARCHAR(MAX) = ''
SELECT @SQL = @SQL 
  + 'ALTER DATABASE [' + [name] + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; '
  + 'DROP DATABASE [' + [name] + ']; '
FROM sys.databases 
WHERE [name] like 'temp_%' AND create_date < DATEADD(day,-7,GETDATE())

-- display statements 
SELECT @SQL

-- execute (uncomment)
--EXEC sp_executesql @SQL 

The above is deleting any databases starting with "temp_" and older than 7 days, but that can be adapted obviously to any situation.

DANGER: Mess up your query, delete some or all of your databases. I left the EXEC statement commented out just to try to avoid someone doing doing this through copy/paste.