SqlServer分离所有用户数据库

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

导读：在实际应用中，有时我们需要一次性分离所有用户数据库，下面给出代码，供大家参考。 USE [ master ] GO IF EXISTS ( SELECT * FROM sys.objects WHERE [ object_id ] = OBJECT_ID (N ' [dbo].[spDetachAllUserDatabases] ' ) AND type IN ( N ' P ' ,N ' PC

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

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

（编辑：李大同）

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