Batch delete database SQL statements and batch Delete SQL statements

Batch delete database SQL statements and batch Delete SQL statements

Because of project requirements, you need to create a database every time you run case. Although it will be deleted at the end of each execution, it is not guaranteed that the deletion will be successful every time (for many reasons, I will not list it ). So I wrote a script to delete databases in batches. First, we should ensure that our database is useful and should not be deleted by mistake. Therefore, the backup must be deleted first.

Back up the database:

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

Delete database:

Method 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

Method 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