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;