[推荐] (SqlServer)分离所有用户数据库

发布时间：2020-12-12 14:47:05 所属栏目：MsSql教程来源：网络整理

导读：[ 推荐 ] (SqlServer) 分离所有用户数据库 —— 通过知识共享树立个人品牌。在实际应用中，有时我们需要一次性分离所有用户数据库，下面给出代码，供大家参考。 ? USE ? [ master ] GO IF ? EXISTS ?(? SELECT ? * ? FROM ?sys.objects ? WHERE ? [ object_

[推荐] (SqlServer)分离所有用户数据库

——通过知识共享树立个人品牌。

在实际应用中，有时我们需要一次性分离所有用户数据库，下面给出代码，供大家参考。

USE? [ master ]
GO
IF? EXISTS?(? SELECT? *
? FROM?sys.objects
? WHERE? [ object_id ]? =? OBJECT_ID(N ' [dbo].[spDetachAllUserDatabases] ')
? AND?type? IN?(?N ' P ',?N ' PC '?)?)?
? DROP? PROCEDURE? [ dbo ]. [ spDetachAllUserDatabases ]
GO

CREATE? PROCEDURE? [ dbo ]. [ spDetachAllUserDatabases ]
AS?
? 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?# Database
?????(
?????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? INTO?# Database?(?DatabaseName?)
????????? SELECT?name
????????? FROM?sys.databases
????????? WHERE?database_id? >? 4
????????? AND?name? NOT? IN?(? ' SQLDBA ',
????????? ' ReportServerTempDB ',
????????? ' distribution '?)?
?????????
????????? -- Set?Variables?for?the?detach?database?loop?
????????? SELECT? @MinDatabaseID? =? MIN(ID),
????????? @MaxDatabaseID? =? MAX(ID)
????????? FROM?# Database
????????
????????? -- Begin?loop?to?detach?databases
????????? 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

????????? -- Get?the?next?DatabaseName?ID
????????? SET? @MinDatabaseID? =? @MinDatabaseID? +? 1
?????????
????????? -- End?Loop
????????? END
????? END
? END

GO

? 2011? EricHu

原创作品，转贴请注明作者和出处，留此信息。

------------------------------------------------

cnBlobs:http://www.cnblogs.com/huyong/
CSDN：http://blog.csdn.net/chinahuyong?

作者：EricHu（DB、CS、BS、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

Q Q：80368704?? E-Mail: 80368704@qq.com
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看?[置顶]索引贴——（不断更新中）

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!