Drop all databases SQL Server
Rédigé par Sozezzo - - Aucun commentaireWe have many ways to drop a database, but if you want to drop many databases.
You can have some problems with opened connections, but this script kill all connection and drop the database.
-- ***************************************** -- -- SQL Script to drop databases -- ***************************************** -- -- PRINT @@servername; PRINT Getdate(); BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH; --#region Select databases to DROP SELECT [name] INTO #dbs FROM sys.databases WHERE database_id > 4 AND name NOT IN ('ReportServer','ReportServerTempDB') --#endregion Select databases to DROP --#region Drop databases USE [MASTER]; SET NOCOUNT ON; DECLARE @name nvarchar(200); DECLARE @sql NVARCHAR(MAX); DECLARE @ToKill AS NVARCHAR(MAX); WHILE (EXISTS(SELECT * FROM #dbs)) BEGIN SELECT TOP 1 @name = name FROM #dbs; DELETE FROM #dbs WHERE @name = name; DECLARE @msg nvarchar(200) = '-- DROP Database : ' + @name; RAISERROR (@msg, 10, 1) WITH NOWAIT; SET @ToKill = ''; 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;' FROM master..sysprocesses WHERE dbid = DB_ID(@name); RAISERROR (@tokill, 10, 1) WITH NOWAIT; EXEC (@tokill); SET @sql = 'ALTER DATABASE [' + @name + '] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;' RAISERROR (@sql, 10, 1) WITH NOWAIT; EXEC (@sql); SET @sql = 'DROP DATABASE [' + @name + ']'; RAISERROR (@sql, 10, 1) WITH NOWAIT; EXEC (@sql); END --#endregion Drop databases
sources:
http://stackoverflow.com/questions/349358/sql-server-print-output-doesnt-appear-immediately
http://stackoverflow.com/questions/5777483/drop-all-databases-from-server