sql mysql 删除数据库_批量删除数据库的SQL语句

由于项目需要，每次运行case的时候都需要创建数据库。虽然每次执行结束都会删除，但是不保证每次都能删除成功(这里有许多原因，我就不列举了)。所以我写了个脚本去批量删除数据库。首先为确保我们的数据库是有用的，不被错删除的。所以就必须先备份在删除。

备份数据库：

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\ABC\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM MASTER.dbo.sysdatabases

WHERE name IN ('E2DB_ABC','Scheduler')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

删除数据库：

方法1：

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer'

OPEN db_cursor

DECLARE @databasename VARCHAR(50) -- database name

DECLARE @databaseCount int =0

FETCH NEXT FROM db_cursor INTO @databasename

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @statement nvarchar(50)

print 'dropping databse: '+@databaseName

exec('

use master

alter database '+@databaseName+' set single_user with rollback immediate

drop database '+@databaseName)

set@databaseCount=@databaseCount+1

FETCH NEXT FROM db_cursor INTO @databasename

END

print 'dropped databse count: '+CONVERT(VARCHAR(12),@databaseCount )

CLOSE db_cursor

DEALLOCATE db_cursor

方法2：

declare @dbToDelete table(name varchar(50))

declare @databaseName varchar(100)

insert into @dbToDelete values('ABC')

insert into @dbToDelete values('DEF')

insert into @dbToDelete values('GH')

insert into @dbToDelete select name from Master..SysDatabases where name like 'ABC%'

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