sqlserver分離所有用戶數(shù)據(jù)庫

1、推薦 (SqlServer)分離所有用戶數(shù)據(jù)庫通過知識共享樹立個人品牌。在實際應用中，有時我們需要一次性分離所有用戶數(shù)據(jù)庫，下面給出代碼，供大家參考。 USE masterGOIF EXISTS ( SELECT *FROM sys.objectsWHERE object_id = OBJECT_ID(Ndbo.spDetachAllUserDatabases)AND type IN ( NP, NPC ) ) DROP PROCEDURE dbo.spDetachAllUserDatabasesGOCREATE PROCEDURE dbo.spDetachAllUserDatabasesAS

2、 BEGIN -Declare Variables DECLARE DatabaseName VARCHAR(100) DECLARE MinDatabaseID INT DECLARE MaxDatabaseID INT DECLARE SQL VARCHAR(4000) -Check for temporary table and drop it if it exists IF OBJECT_ID(tempDB.dbo.#Database) IS NOT NULL DROP TABLE #Database; -Create temporary table CREATE TABLE #Dat

3、abase ( ID INT IDENTITY(1, 1), DatabaseName VARCHAR(100) ) -Check for existing user databases IF EXISTS ( SELECT name FROM sys.databases WHERE database_id 4 AND name NOT IN ( SQLDBA, ReportServer, ReportServerTempDB, distribution ) ) BEGIN -Insert all database names into a temporary table INSERT INT

4、O #Database ( DatabaseName ) SELECT name FROM sys.databases WHERE database_id 4 AND name NOT IN ( SQLDBA, ReportServer, ReportServerTempDB, distribution ) -Set Variables for the detach database loop SELECT MinDatabaseID = MIN(ID), MaxDatabaseID = MAX(ID) FROM #Database -Begin loop to detach database

5、s WHILE MinDatabaseID = MaxDatabaseID BEGIN -Get DatabaseName SELECT DatabaseName = DatabaseName FROM #Database WHERE ID = MinDatabaseID -Build Detach Database Command SET SQL = EXEC sp_detach_db + + DatabaseName + + ; -Try Catch block to execute SQL and handle errors BEGIN TRY -Detach Database EXEC ( SQL ) PRINT Detached + DatabaseName END TRY BEGIN CATCH SELECT DatabaseName, message_id, severity, text, SQL FROM sys.messages WHERE message_id = ERROR AND language_id = 1033 -British English END CATCH -