SQL Server 一鍵分離所有使用者資料庫：自動化預存程序完整代碼

適用場景

資料庫搬遷、版本升級、災難演練前，需一次分離所有使用者資料庫。手工逐一定極易遺漏與出錯，本文提供自動化預存程序 spDetachAllUserDatabases，自動排除系統庫與常見非使用者庫，含錯誤捕獲與列印進度。

預存程序完整代碼

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
    SET NOCOUNT ON;

    DECLARE @DatabaseName VARCHAR(100),
            @MinDatabaseID INT,
            @MaxDatabaseID INT,
            @SQL VARCHAR(4000);

    -- 建立暫存表
    IF OBJECT_ID('tempdb.dbo.#Database') IS NOT NULL
        DROP TABLE #Database;

    CREATE TABLE #Database (
        ID INT IDENTITY(1,1),
        DatabaseName VARCHAR(100)
    );

    -- 篩選使用者資料庫 (database_id > 4 且非特定系統庫)
    IF EXISTS (
        SELECT name FROM sys.databases
        WHERE database_id > 4
          AND name NOT IN ('SQLDBA', 'ReportServer', 'ReportServerTempDB', 'distribution')
    )
    BEGIN
        INSERT INTO #Database (DatabaseName)
        SELECT name FROM sys.databases
        WHERE database_id > 4
          AND name NOT IN ('SQLDBA', 'ReportServer', 'ReportServerTempDB', 'distribution');

        SELECT @MinDatabaseID = MIN(ID), @MaxDatabaseID = MAX(ID) FROM #Database;

        WHILE @MinDatabaseID <= @MaxDatabaseID
        BEGIN
            SELECT @DatabaseName = DatabaseName FROM #Database WHERE ID = @MinDatabaseID;

            SET @SQL = 'EXEC sp_detach_db ''' + @DatabaseName + ''';';

            BEGIN TRY
                EXEC(@SQL);
                PRINT 'Detached ' + @DatabaseName;
            END TRY
            BEGIN CATCH
                SELECT @DatabaseName AS FailedDB,
                       ERROR_NUMBER() AS ErrNo,
                       ERROR_SEVERITY() AS Severity,
                       ERROR_MESSAGE() AS ErrMsg,
                       @SQL AS ExecutedSQL;
            END CATCH

            SET @MinDatabaseID = @MinDatabaseID + 1;
        END
    END
END;
GO

使用方式

EXEC spDetachAllUserDatabases;

關鍵設計點

1. 排除清單：系統 4 大庫 (master, model, msdb, tempdb) 由 database_id > 4 自動排除；另手工排除 SQLDBA, ReportServer, ReportServerTempDB, distribution 等常見非使用者庫。
2. 暫存表 + 遊標替代：用身分識別欄位 ID 模擬迴圈，避免遊標開銷。
3. TRY...CATCH：單庫失敗不中斷整體流程，記錄錯誤碼、訊息與實際執行 SQL 便於事後排查。
4. PRINT 進度：控制台即時顯示已分離/失敗資料庫名。

注意事項

• 執行前確認無活躍連線，否則 sp_detach_db 會失敗 (除非加 @skipchecks=true 強制，但不建議)。
• 分離後資料庫檔案 (.mdf/.ldf) 保留於磁碟，可直接附加還原。
• 生產環境建議先備份再執行。