批量删除数据库的SQL语句

来源：互联网发布：支持向量机算法实例编辑：程序博客网时间：2024/05/24 08:33

由于项目需要，每次运行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 ('E1DB_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 =0FETCH NEXT FROM db_cursor INTO @databasename  WHILE @@FETCH_STATUS = 0   BEGIN  DECLARE @statement nvarchar(50)print 'dropping databse: '+@databaseNameexec('use masteralter database '+@databaseName+' set single_user with rollback immediatedrop 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)beginselect top 1 @databaseName=name from @dbToDeleteif exists(select * from sys.databases where name = @databaseName)beginprint 'dropping databse: '+@databaseNameexec('use masteralter database '+@databaseName+' set single_user with rollback immediatedrop database '+@databaseName)enddelete from @dbToDelete where name = @databaseNameend

1 0