sqlserver分离所有用户数据库

1、 (SqlServer)分离所有用户数据库通过知识共享树立个人品牌。在实际应用中，有时需要分离所有用户数据库，下面给出代码，供大家参考。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-