刪除庫名包含dw_的數(shù)據(jù)庫

if object_id('tempdb..#dbToDelete') is not null?

? ? drop table #dbToDelete
if object_id('tempdb..#databaseName') is not null?
? ? drop table #databaseName
declare @dbToDelete table(name varchar(50))
declare @databaseName varchar(100)

insert into @dbToDelete select name from sys.databases where name like '%dw_%'

--刪除指定字符開頭，把前面的%號刪除掉

while exists(select name from @dbToDelete)
begin
?
? ? select top 1 @databaseName=name from @dbToDelete
?
? ? if exists(select * from sys.databases where name = @databaseName)
? ? begin
? ? ? ? print 'dropping databse: '+@databaseName
?
? ? ? ? --強制刪除所有符合條件的實例
? ? ? ? ? exec('
? ? ? ? use master
? ? ? ? ALTER DATABASE ' + @databaseName +' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
? ? ? ? drop database '+@databaseName)
? ? ? ??
? ? end
? ? ?
? ? delete from @dbToDelete where name = @databaseName
end