How to drop multiple databases in SQL Server

Question

Just to clarify, ths isn't really a question, more some help for people like me who were looking for an answer.
A lot of applications create temp tables and the like, but I was surprised when Team Foundation Server created 80+ databases on my test SQL Server. TFS didn't install correctly, and kindly left me to clear up after it. Since each database had a naming convention, rather than delete each database by hand, I remembered how to use cursors and have written what I view to be the most unwise piece of T-SQL ever:

   CREATE TABLE #databaseNames (name varchar(100) NOT NULL, db_size varchar(50), owner varchar(50), dbid int, created date, status text, compatibility_level int);
INSERT #databaseNames
    exec sp_helpdb;

DECLARE dropCur CURSOR FOR
    SELECT name FROM #databaseNames WHERE name like '_database_name_%';
OPEN dropCur;
DECLARE @dbName nvarchar(100);
FETCH NEXT FROM dropCur INTO @dbName;
DECLARE @statement nvarchar(200);
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @statement = 'DROP DATABASE ' + @dbName;
    EXEC sp_executesql @statement;
    FETCH NEXT FROM dropCur INTO @dbName;
END
CLOSE dropCur;
DEALLOCATE dropCur;
DROP TABLE #databaseNames;

It goes without saying that using cursors like this is probably really dangerous, and should be used with extreme caution. This worked for me, and I haven't seen any further damage to my database yet, but I disclaim: use this code at your own risk, and back up your vital data first!
Also, if this should be deleted because it's not a question, I understand. Just wanted to post this somewhere people would look.

Answer 1

Why not just do this instead?

USE master;
Go
SELECT 'DROP DATABASE ['+ name + ']' 
FROM sys.databases WHERE name like '_database_name_%';
GO

Capture the output of that resultset and then paste it into another query window. Then run that. Why write all this TSQL cursor code?

"When you have a hammer, everything looks like a nail!"..

Answer 2

this is easy...

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 'name.of.db%'
if len(@dbnames) = 0
    begin
    print 'no databases to drop'
    end
else
    begin
    set @statement = 'drop database ' + substring(@dbnames, 2, len(@dbnames))
    print @statement
    exec sp_executesql @statement
    end

Answer 3

There is no need to use a cursor, and no need to copy and paste SQL statements. Just run these two lines:

DECLARE @Sql as NVARCHAR(MAX) = (SELECT 'DROP DATABASE ['+ name + ']; ' FROM sys.databases WHERE name LIKE 'DBName%' FOR XML PATH(''))

EXEC sys.sp_executesql @Sql

Of course, any DB matching the criteria will be dropped immediately, so be sure that you know what you are doing.

Answer 4

I attempted to use the solution suggested by Morty Wild but it resulted in this error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I modified the query a bit and got it to work with the following:

DECLARE @Sql as NVARCHAR(MAX) = (SELECT 'DROP DATABASE '+ STRING_AGG(name, ', ') + '; ' FROM sys.databases WHERE name like 'TEST_DB_%'); EXEC sys.sp_executesql @Sql

Though I would add this, if some one experience the same. I run sqlcmd version 17.10.0001.1 Linux

Answer 5

I really like OFH's answer. However you may run into issues when you use certain SQL tools or have other active connections to the databases you want to drop. The following extension will mitigate that:

USE master;
GO
SELECT 'ALTER DATABASE ['+ name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ['+ name + '];' 
FROM sys.databases WHERE name LIKE '_database_name_%';
GO

As answered in a semi-related question (How to drop a database when it's currently in use?) by Kaushik Maheta, setting the db to single-user mode first will ensure that no active connection interferes with the drop statement.