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

SQL Server 中繼資料管理：建立應用程式對應資料庫的元資料倉儲

編輯：關於SqlServer

痛點：資料庫名稱 ≠ 應用程式名稱

DBA 看到 DB_FIN_2023，業務主管只認得「財務系統」
停機通知、容量規劃、授權審計都需要「應用程式視角」
命名規範不一、歷史包袱重，靠人工對應不可維護

解決方案：元資料倉儲 + 應用程式對應表

核心兩表設計

-- 應用程式主檔
CREATE TABLE dbo.Applications (
    AppID INT IDENTITY(1,1) PRIMARY KEY,
    ApplicationName VARCHAR(100) NOT NULL UNIQUE
);

-- 資料庫-應用程式對應（故意反正規化，避免 JOIN、支援每日重載）
CREATE TABLE dbo.Database_Applications (
    DB_AppID INT IDENTITY(1,1) PRIMARY KEY,
    ServerName VARCHAR(50) NOT NULL,
    DatabaseName VARCHAR(100) NOT NULL,
    ApplicationName VARCHAR(100) NULL,  -- 允許 NULL 代表待對應
    CONSTRAINT UQ_DB_App UNIQUE (ServerName, DatabaseName)
);

設計決策理由

資料量小：~800 資料庫 × 80 實例，無效能壓力
直接查詢：報表只查 Database_Applications 即可，不需 JOIN Applications
每日焦土重載：除 Agent/備份歷史外，每日 TRUNCATE + 重載，Database_Applications 保留人工對應關係

自動化發現新資料庫腳本

-- 每日資料收集後執行，找出尚未對應的新資料庫
SELECT D.ServerName, D.DatabaseName
FROM dbo.Databases D
LEFT JOIN dbo.Database_Applications DA
    ON D.ServerName = DA.ServerName AND D.DatabaseName = DA.DatabaseName
WHERE DA.DB_AppID IS NULL
ORDER BY D.ServerName, D.DatabaseName;

結果可直接餵給 SSRS 報表、Email 警報、Teams/Slack Webhook
DBA 每日審核清單，補齊 ApplicationName

合併新對應關係的預存程序

CREATE PROCEDURE dbo.pAdd_Application
    @ServerName VARCHAR(50),
    @DatabaseName VARCHAR(100),
    @ApplicationName VARCHAR(100)
AS
BEGIN
    -- 更新現有未對應記錄
    UPDATE dbo.Database_Applications
    SET ApplicationName = @ApplicationName
    WHERE ServerName = @ServerName AND DatabaseName = @DatabaseName
      AND ApplicationName IS NULL;

    -- 新增應用程式主檔（若不存在）
    IF NOT EXISTS (SELECT 1 FROM dbo.Applications WHERE ApplicationName = @ApplicationName)
    BEGIN
        INSERT INTO dbo.Applications (ApplicationName) VALUES (@ApplicationName);
        PRINT '新增應用程式: ' + @ApplicationName;
    END

    -- 確認結果
    SELECT ServerName, DatabaseName, ApplicationName
    FROM dbo.Database_Applications
    WHERE ServerName = @ServerName AND DatabaseName = @DatabaseName;
END