sqlserver删除指定字符的数据库

删除库名包含dw_的数据库

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