36

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.

Kara
  • 6,115
  • 16
  • 50
  • 57
Gargravarr
  • 635
  • 2
  • 10
  • 18
  • 2
    You are allowed to answer your own questions, so it might be better to re-write the question into an actual question (try to phrase it both in terms of your particular predicament, and the general problem it fits within), then post the script as an answer. – Damien_The_Unbeliever Feb 25 '11 at 11:53

3 Answers3

79

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!"..

Sam Holder
  • 32,535
  • 13
  • 101
  • 181
OFH
  • 826
  • 1
  • 5
  • 2
  • 6
    This a great solution for manual database dropping. I have a lot of databases generated during test, and when I abort test run I usually end up with 10-20 databases. Generating this output enables me to easily review what will be dropped. I would only suggest to modify the output to `SELECT 'DROP DATABASE ['+ name +']'` to take special characters in db names into account, which was a fix that I needed to do. – nohwnd Sep 21 '16 at 12:07
  • The problem with this is that if you don't close the connection before the deletion, the deletion may take a while even though you may not care about the completed connections. – Tarik Jan 18 '17 at 16:19
39

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
SeriousM
  • 3,374
  • 28
  • 33
2

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.

Monty Wild
  • 3,981
  • 1
  • 21
  • 36