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

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

2、re VariablesDECLARE DatabaseName VARCHAR(100)DECLARE MinDatabaseID DECLARE MaxDatabaseID DECLARE SQL VARCHAR(4000)-Check for temporary table and drop it if it exists OBJECT_ID(tempDB.dbo.#Database) IS NOT NULLDROP TABLE #Database;IF-Create temporary table(CREATE TABLE #DatabaseIDIDENTITY(1, 1),Datab

3、aseName VARCHAR(100)-Check for existing user databasesIF EXISTS ( SELECT nameFROM sys.databases WHERE database_id 4AND name NOT IN ( SQLDBA, ReportServer, ReportServerTempDB,distribution ) )BEGIN-Insert all database names #Database ( DatabaseName )SELECT name FROM sys.databasesWHERE database_id 4o a

4、 temporary tableINSERTOAND name NOT IN ( SQLDBA, ReportServer, ReportServerTempDB,distribution )-Set Variables for the detach database loop MinDatabaseID = MIN(ID),MaxDatabaseID = MAX(ID)FROM #DatabaseSELECT-Begin loop to detach databases MaxDatabaseIDBEGINWHILE MinDatabaseID =-Get DatabaseNameFROM

5、#DatabaseSELECT DatabaseName = DatabaseNameWHERE ID = MinDatabaseID-Build Detach Database sp_detach_db + + DatabaseName+ + ;dSET SQL = EXEC-Try Catch block to execute SQL and handle errorsBEGRY-Detach DatabaseEXEC ( SQL)PRDetached + DatabaseNameEND TRY BEGIN CATCHSELECT DatabaseName, message_id,severity, text, SQLFROM sys.messagesWHERE message_id = ERRORAND language_id = 1033 -British EnglishEND CATCH-