Delete multiple databases in SQL service using a filter

Below is a query to delete multiple databases where the database name matches the string specified:

Replace the <insert database name string> with the database name filter.

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 ‘<insert database name string>%’
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

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s