How can I quickly delete all databases whose name matches a patttern?

Question

I have many databases that on my SQL Server box that start with a prefix zzz.

Is there a way to do a DROP DATABASE (or some other method) that will remove and delete the data files? If a connection is opened, I want it closed.

Basically I just want them gone.

Look at sys.databases for the list of databases. Then drop them. — Sean Lange, Jul 20 '15 at 16:19
One option, if you'd prefer not to write anything, would be, in SSMS, hit view -> Object Explorer Details, and from here, you can highlight / select multiple, and delete — Dan, Jul 20 '15 at 16:21

score 2 · Answer 1 · answered Jul 20 '15 at 16:30

2

Generate a drop script, copy/paste & run:

exec master.sys.sp_msforeachdb 'if ''?'' like ''ZZZ%'' print ''drop database [?]'''

Or drop directly in the SQL string if your brave.

answered Jul 20 '15 at 16:30

Alex K.

171,639
30
264
288

That procedure is none to occasionally miss databases. It is an undocumented procedure that had proven to be unreliable too. Here is a good discussion the topic. https://sqlblog.org/2018/10/22/sp-ineachdb-2 – Sean Lange Jul 20 '15 at 16:33
I read this as a one of quick fix, likely good enough for that – Alex K. Jul 20 '15 at 16:37
Accepted due to simplicity. I appreciate the other responses however. – WhiskerBiscuit Jul 20 '15 at 19:17

score 1 · Answer 2 · edited May 23 '17 at 11:44

You could write a dynamic SQL to do this:

use master
go
declare @dbnames nvarchar(max)
declare @statement nvarchar(max)
declare @closeconnection nvarchar(max)
set @dbnames = ''
set @statement = ''
select @dbnames = @dbnames + ',[' + name + ']' from sys.databases where name like 'zzz%'
if len(@dbnames) = 0
    begin
    print 'no databases to drop'
    end
else
    BEGIN
    SET @closeconnection = 'alter database ' + substring(@dbnames, 2, len(@dbnames))
                            + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
    set @statement = 'drop database ' + substring(@dbnames, 2, len(@dbnames))
    print @statement
    EXEC sp_executesql @closeconnection;
    exec sp_executesql @statement;
    end

Normally, the syntax to close all active connections to a database is:

  --set it to single user to disable any other connections
 ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE  

 --do your stuff here

 --set it back to multiple users
 ALTER DATABASE YourDatabase SET MULTI_USER

Alternatively, you could also generate a dynamic select list that populates your drop database statement along with close connection statements like this:

USE master;
Go
SELECT 'DROP DATABASE '+ name, 
       'alter database ' + name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
FROM sys.databases WHERE name like 'zzz%';
GO

Courtesy: @SeriousM and OFH

score 1 · Accepted Answer · answered Jul 20 '15 at 16:33

1

use master;
go

-- this will drop all dbs that start with t5....

declare @strsql varchar(500)
declare @curname sysname
select @curname = name from sys.databases
where name like 't5%'

while( @@rowcount> 0)
begin 
set @strsql ='ALTER DATABASE ' +@curname +' SET OFFLINE WITH ROLLBACK IMMEDIATE'
exec (@strsql)
set @strsql ='drop database '+@curname
exec (@strsql)
select @curname = name from sys.databases
where name like 't5%'
end

answered Jul 20 '15 at 16:33

benjamin moskovits

5,261
1
12
22

This will only delete one database, and it leaves behind the MDF and LDF files as well – WhiskerBiscuit Jul 20 '15 at 19:32
I created two databases named t5a and t5b and then ran my script. Both databases were dropped from sys.databases and were removed from the instance. To have my script remove the mdf/ldf files change the script from SET OFFLINE WITH ROLLBACK IMMEDIATE to the ALTER DATABASE [databasename] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; By not removing the files immediately you can can undo the delete. – benjamin moskovits Jul 20 '15 at 23:05

score 0 · Answer 4 · answered Jul 23 '15 at 14:57

I modified benjamin's script a bit so you only have to declare the prefix once.

use master;
declare @dbPrefix varchar(10)
set @dbPrefix = 'zzz_%';

declare @strsql varchar(500)
declare @curname sysname
select @curname = name from sys.databases
where name like @dbPrefix

while( @@rowcount> 0)
    begin 
        set @strsql ='ALTER DATABASE ' +@curname +' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
        exec (@strsql)
        set @strsql ='drop database '+@curname
        exec (@strsql)
        select @curname = name from sys.databases
        where name like @dbPrefix
    end

How can I quickly delete all databases whose name matches a patttern?

4 Answers4