程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> Sql Server 分離所有用戶數據庫

Sql Server 分離所有用戶數據庫

編輯:關於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', 'ReportServer',
'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
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved