detach_AllDB

批量处理脚本将所有DB按照不同的类型进行detach操作

USE [master]
GO
--Detach database 

SET NOCOUNT ON 

DECLARE @DbName sysname
DECLARE Cur_Dtch CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR 
	SELECT name
	FROM sys.databases WITH (NOLOCK)
	WHERE name NOT IN ('master', 'tempdb'
					, 'model', 'msdb'
					, 'pubs', 'tempdb'
					, 'northwind','distribution') 
OPEN Cur_Dtch
FETCH NEXT FROM Cur_Dtch INTO @DbName
WHILE @@FETCH_STATUS=0
BEGIN
	--whether publish
	IF EXISTS(SELECT TOP 1 * FROM sys.databases WITH (NOLOCK)
				WHERE name =@DbName AND is_published=1
				AND source_database_id IS NULL )
		BEGIN
			PRINT 'Processing  publish  ' +@DbName
			EXEC dbo.sp_replicationdboption @DbName,'publish','false'
		END

	--whether subscribe
	IF EXISTS(SELECT TOP 1 * FROM sys.databases WITH (NOLOCK)
				WHERE name =@DbName AND is_subscribed=1
				AND source_database_id IS NULL )
	BEGIN
		PRINT 'Processing  subscribe  ' +@DbName
		EXEC dbo.sp_replicationdboption @DbName,'subscribe','false'
	END

	
	--whether database mirroring
	IF EXISTS(SELECT TOP 1 * FROM sys.database_mirroring WITH (NOLOCK)
				WHERE database_id =DB_ID(@DbName)
				AND  mirroring_role IS NOT NULL )
	BEGIN
		PRINT 'Processing mirroring  ' +@DbName
		EXEC ('ALTER DATABASE ' +@DbName+' SET PARTNER OFF')
	END
	
	
	--whether single user
	IF EXISTS(SELECT TOP 1 * FROM sys.databases WITH (NOLOCK)
				WHERE name =@DbName AND user_access<>1
			 )
	BEGIN
		PRINT 'Processing single user  ' +@DbName
		EXEC ('ALTER DATABASE ' +@DbName+' SET SINGLE_USER ')
	END


	--detach
	EXEC dbo.sp_detach_db @DbName

	FETCH NEXT FROM Cur_Dtch INTO @DbName
END
CLOSE  Cur_Dtch
DEALLOCATE  Cur_Dtch