您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

善用 SQL-DMO 自動化資料庫結構升級與部署

編輯：關於SqlServer

痛點：手工升級的惡夢

應用系統升級時，資料庫結構變更 (新增欄位、修改預存程序、檢視表) 常成為瓶頸。syscomments 文字長度限制 8000 字元、換行符處理困難、物件相依順序難以自動解析，導致手工產生/執行腳本極易出錯。

SQL-DMO Transfer 物件：強大的自動化引擎

SQLDMO.Transfer 可將來源資料庫的結構 (含相依關係) 輸出為記憶體腳本，直接在目標資料庫執行，完美解決：

大型腳本無長度限制 (記憶體流)。
自動處理物件建立順序 (相依性解析)。
支援跨伺服器、跨執行個體部署。

核心屬性與方法

屬性	說明
CopyAllObjects / CopyAllTables / CopyAllStoredProcedures / CopyAllViews / CopyAllTriggers / CopyAllDefaults / CopyAllRules / CopyAllUserDefinedDatatypes	控制複製哪類物件
DestServer, DestDatabase, DestLogin, DestPassword, DestUseTrustedConnection	目標連線資訊
DropDestObjectsFirst	先刪除目標同名物件 (危險，建議 False 配合腳本比對)
IncludeDependencies	包含相依物件 (必開啟)
ScriptType	腳本類型 (建立/刪除/修改)

方法：AddObject, AddObjectByName 精確控制納入物件。

實戰範例：以來源資料庫為藍本升級目標資料庫 (T-SQL 呼叫 OLE Automation)

CREATE PROCEDURE dbo.usp_UpgradeDatabase
    @Source_DB sysname, @Des_DB sysname, @UserName sysname, @psw sysname
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @hr int, @SrvID int, @DBsId int, @SDBId int, @DDBId int, @TransferID int, @Error int;

    -- 建立 SQLDMO.SQLServer 物件
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SrvID OUTPUT;
    IF @hr <> 0 GOTO ErrHandler;

    -- 連線本機伺服器 (亦可指定遠端)
    EXEC @hr = sp_OAMethod @SrvID, 'Connect', NULL, @@SERVERNAME, @UserName, @psw;
    IF @hr <> 0 GOTO ErrHandler;

    -- 取得 Databases 集合
    EXEC @hr = sp_OAGetProperty @SrvID, 'Databases', @DBsId OUTPUT;

    -- 取得來源/目標資料庫物件
    EXEC @hr = sp_OAMethod @DBsId, 'Item', @SDBId OUTPUT, @Source_DB;
    EXEC @hr = sp_OAMethod @DBsId, 'Item', @DDBId OUTPUT, @Des_DB;

    -- 建立 Transfer 物件並設定屬性
    EXEC @hr = sp_OACreate 'SQLDMO.Transfer', @TransferID OUTPUT;
    EXEC @hr = sp_OASetProperty @TransferID, 'DestServer', @@SERVERNAME;
    EXEC @hr = sp_OASetProperty @TransferID, 'DestLogin', @UserName;
    EXEC @hr = sp_OASetProperty @TransferID, 'DestPassword', @psw;
    EXEC @hr = sp_OASetProperty @TransferID, 'DestDatabase', @Des_DB;
    EXEC @hr = sp_OASetProperty @TransferID, 'CopyAllStoredProcedures', 1;
    EXEC @hr = sp_OASetProperty @TransferID, 'CopyAllTriggers', 1;
    EXEC @hr = sp_OASetProperty @TransferID, 'CopyAllViews', 1;
    EXEC @hr = sp_OASetProperty @TransferID, 'IncludeDependencies', 1;
    -- 執行傳輸：以目標資料庫物件呼叫 Transfer 方法
    EXEC @hr = sp_OAMethod @DDBId, 'Transfer', NULL, @TransferID;

    -- 清理
    EXEC sp_OADestroy @TransferID;
    EXEC @hr = sp_OAMethod @SrvID, 'DisConnect';
    EXEC sp_OADestroy @SrvID;
    RETURN 0;

ErrHandler:
    EXEC sp_OADestroy @SrvID;
    RETURN @hr;
END;

遺留挑戰：物件相依順序

雖 IncludeDependencies 能處理多數情況，但複雜巢狀檢視表、函數相依仍可能失敗。建議搭配 sys.sql_expression_dependencies 拓撲排序產生腳本，或採用現代化工具 (SSDT、DbUp、Flyway) 取代 SQL-DMO (已停止更新，SQL 2012 後移除)。