SqlServer: dropping a subset of databases

While the [exec sp_MSforeachdb] way is attractive because it is so terse, it’s probably unsupported.

So this is a better path I think, and I thank this person [Pellared] for doing the work. For my purposes, I have just added in that I want to be more selective and to restrict the dropping to databases which start with a given prefix.

We start with the db explorer in this state:

We run this:

and we end up with this, i.e. the databases starting with [test_] have gone:

declare @command nvarchar(max);
set @command = '';

select @command = @command
+ 'alter database [' + [name] + '] set single_user with rollback immediate;' 
+ 'drop database [' + [name] +'];'
from [master].[sys].[databases] 
 where ( [name] not in ( 'master', 'model', 'msdb', 'tempdb')) and ([name] like 'test_%')

select @command;
execute sp_executesql @command;



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s