Detach databases

Detach all the database on the current SQL instance.
created by on 2013-01-18
/**
 * Print out SQL statements for taking
 * all databases on a given SQL Server offline
 */

SET NOCOUNT ON

/* esclude system databases from the list */
DECLARE @SystemDatabases AS TABLE(DBName nvarchar(50))
INSERT INTO @SystemDatabases(DBName) VALUES(N'master')
INSERT INTO @SystemDatabases(DBName) VALUES(N'tempdb')
INSERT INTO @SystemDatabases(DBName) VALUES(N'DBA')
INSERT INTO @SystemDatabases(DBName) VALUES(N'LiteSpeedLocal')
INSERT INTO @SystemDatabases(DBName) VALUES(N'model')
INSERT INTO @SystemDatabases(DBName) VALUES(N'msdb')
INSERT INTO @SystemDatabases(DBName) VALUES(N'SSODB')

/* get a list of all databases */
DECLARE @Databases AS TABLE(DBName nvarchar(50))
INSERT INTO @Databases(DBName)
SELECT name FROM sys.databases WHERE name NOT IN (SELECT * FROM @SystemDatabases)

/* Assemble the SQL top take all the databases offline */
DECLARE @Databasename AS nvarchar(50)
WHILE (EXISTS(SELECT TOP 1 DBName FROM @Databases))
BEGIN
	SELECT TOP 1 @Databasename = DBName FROM @Databases
	
	PRINT N'GO'
	PRINT N'ALTER Database [' + @Databasename + '] SET OFFLINE WITH ROLLBACK IMMEDIATE'
	PRINT N'GO'
	PRINT N''
	
	DELETE FROM @Databases WHERE DBName = @Databasename
END
Tags:
Fork allmark on GitHub