[转载]SqlServer分离所有用户数据库

[转载][推荐] (SqlServer)分离所有用户数据库 – 海南.胡勇 – 博客园.

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

USE [master]
GO
IF EXISTSSELECT *
FROM sys.objects
WHERE [object_id] = OBJECT_ID(N[dbo].[spDetachAllUserDatabases])
AND type IN ( NP, NPC ) )
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.#DatabaseIS NOT NULL
DROP TABLE [#Database];

Create temporary table
CREATE TABLE #Database
(
ID INT IDENTITY(11),
DatabaseName VARCHAR(100)
)

Check for existing user databases
IF EXISTSSELECT name
FROM sys.databases
WHERE database_id > 4
AND name NOT INSQLDBAReportServer,
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 INSQLDBAReportServer,
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

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

————————————————

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏