Drop All Databases SQL Server

 1
 2-- ***************************************** --
 3--       SQL Script to drop databases
 4-- ***************************************** --
 5--
 6PRINT @@servername;
 7PRINT Getdate();
 8BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
 9
10--#region Select databases to DROP
11
12SELECT [name] INTO #dbs
13FROM sys.databases
14WHERE
15    database_id > 4
16    AND name NOT IN ('ReportServer','ReportServerTempDB')
17
18--#endregion Select databases to DROP
19
20--#region Drop databases
21
22USE [MASTER];
23SET NOCOUNT ON;
24DECLARE @name nvarchar(200);
25DECLARE @sql NVARCHAR(MAX);
26DECLARE @ToKill AS NVARCHAR(MAX);
27
28WHILE (EXISTS(SELECT *
29    FROM #dbs))
30BEGIN
31
32    SELECT TOP 1 @name = name
33    FROM #dbs;
34    DELETE FROM #dbs
35    WHERE
36        @name = name;
37
38    DECLARE @msg nvarchar(200) = '-- DROP Database : ' + @name;
39    RAISERROR (@msg, 10, 1) WITH NOWAIT;
40
41    SET @ToKill = '';
42    SELECT @toKill = @ToKill + 'begin try kill ' + cast(spid AS NVARCHAR(10)) + '; print ''Ok   to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end try begin catch print ''Fail to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end catch;'
43    FROM master..sysprocesses
44    WHERE dbid = DB_ID(@name);
45
46    RAISERROR (@tokill, 10, 1) WITH NOWAIT;
47    EXEC (@tokill);
48
49    SET @sql = 'ALTER DATABASE [' + @name + '] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;'
50    RAISERROR (@sql, 10, 1) WITH NOWAIT;
51    EXEC (@sql);
52
53    SET @sql = 'DROP DATABASE [' + @name + ']';
54    RAISERROR (@sql, 10, 1) WITH NOWAIT;
55    EXEC (@sql);
56
57END
58
59--#endregion Drop databases