自動化匯出 SQL Server 物件相依關係至 Excel：C# 實作完整方案

專案背景

• 舊系統：200 張表、最大表 2.6 億列、日增 50 萬、925 支預存程序。
• 高耦合、人員流動大、新人接手需快速掌握相依圖譜。
• 需求：Excel 輸出所有 SP/函數/檢視表的相依物件 (表、函數、檢視、SP)，按類型/名稱排序。

技術方案選型

核心邏輯流程

1. 連線 `msdb`/目標資料庫。
2. 查詢 `sys.objects` 取得所有 SP (`P`)、函數 (`FN,IF,TF`)、檢視 (`V`)，排除系統圖表 SP。
3. 逐一呼叫 `sp_depends` 取得相依結果集。
4. 去重 (同名物件多行)、移除 `dbo.` 前綴、按 `Type` 後 `Name` 排序 (自訂 `IComparer`)。
5. 寫入 Excel：每類物件分區塊，標題列灰底粗體，相依列淺灰，類型欄黃底。
6. 儲存 `.xls`，檔名含資料庫名。

關鍵程式碼片段

主流程

var connStr = $"Password={pwd};User ID={user};Initial Catalog={db};Data Source={server};";
using (var conn = new SqlConnection(connStr)) {
    conn.Open();
    var xls = new ExcelFile();
    var ws = xls.Worksheets.Add("Dictionary");
    int row = 5; // 標題列在第 5 行

    // 寫入標題樣式
    var titleStyle = new CellStyle(xls) { Font = { Weight = ExcelFont.BoldWeight } };
    titleStyle.FillPattern.SetSolid(Color.DarkGray);
    ws.Cells[0, 0].Value = $"{db} Database Dictionary";
    ws.Cells[4, 0].Value = "Name"; ws.Cells[4, 0].Style = titleStyle;
    ws.Cells[4, 1].Value = "Dependencies"; ws.Cells[4, 1].Style = titleStyle;
    ws.Cells[4, 2].Value = "Type"; ws.Cells[4, 2].Style = titleStyle;

    // 處理三類物件
    ProcessObjects(conn, "'P'", "Stored Procedures", systemSpList, ws, ref row);
    ProcessObjects(conn, "'FN','IF','TF'", "Functions", new List(), ws, ref row);
    ProcessObjects(conn, "'V'", "Views", new List(), ws, ref row);

    xls.SaveXls(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"{db}.xls"));
}

處理單一類別物件

void ProcessObjects(SqlConnection conn, string types, string category, 
    List ignoreList, ExcelWorksheet ws, ref int row) {
    // 寫入類別標題
    var catStyle = new CellStyle(xls) { Font = { Weight = ExcelFont.BoldWeight } };
    catStyle.FillPattern.SetSolid(Color.Yellow);
    ws.Cells[row++, 0].Value = category; ws.Cells[row-1, 0].Style = catStyle;

    var dt = new DataTable();
    using (var cmd = new SqlCommand($"SELECT name FROM sys.objects WHERE xtype IN ({types}) ORDER BY name", conn))
    using (var da = new SqlDataAdapter(cmd)) da.Fill(dt);

    foreach (DataRow r in dt.Rows) {
        string objName = r["name"].ToString();
        if (ignoreList.Contains(objName)) continue;

        ws.Cells[row, 0].Value = objName;
        ws.Cells[row, 0].Style = new CellStyle { FillPattern = { PatternType = FillPatternStyle.Solid, ForegroundColor = Color.LightGray } };
        
        var depDt = new DataTable();
        using (var cmd2 = new SqlCommand($"EXEC sp_depends '{objName}'", conn))
        using (var da2 = new SqlDataAdapter(cmd2)) da2.Fill(depDt);

        var uniqueDeps = new Dictionary(); // Name -> Type
        foreach (DataRow dr in depDt.Rows) {
            string depName = dr["name"].ToString();
            if (depName.StartsWith("dbo.", StringComparison.OrdinalIgnoreCase)) depName = depName.Substring(4);
            if (!uniqueDeps.ContainsKey(depName)) uniqueDeps[depName] = dr["type"].ToString();
        }

        var sorted = uniqueDeps.Select(kvp => new KeyValuePair(kvp.Key, kvp.Value))
                               .OrderBy(kvp => kvp.Value).ThenBy(kvp => kvp.Key).ToList();
        foreach (var dep in sorted) {
            ws.Cells[++row, 1].Value = dep.Key;
            ws.Cells[row, 2].Value = dep.Value;
        }
        row += 3; // 類別間留白
    }
}

實戰踩坑與優化

• `sp_depends` 遺漏相依：動態 SQL、跨資料庫、延遲繫結物件可能不顯示。緩解：對被遺漏物件執行 `ALTER ... RECOMPILE` 或加空白行重建，刷新相依中繼資料。
• 重複記錄：`sp_depends` 回傳重複行，必須用 Dictionary 去重。
• 排序失效：`DataTable.Select` 多欄位排序有時不穩，改用 LINQ `OrderBy/ThenBy` (需 .NET 3.5+) 或手寫 `IComparer` (本專案採用)。
• 效能：925 支 SP 循環呼叫 `sp_depends` 耗時數分鐘，可接受。大規模可改用 `sys.sql_expression_dependencies` 單查詢完成。
• Excel 列數限制：免費版 GemBox 限 150 列/工作表，超過需分多工作表或購買授權。輸出 6000+ 列需分頁處理。

擴充方向

• 支援 `sys.sql_expression_dependencies` (SQL 2008+) 更完整相依分析 (含欄位級)。
• 輸出 GraphViz DOT 格式，生成視覺化相依圖。
• 整合至 CI/CD：每次部署自動生成相依文件，版本化管理。
• Web API 介面：前端按需查詢單一物件上下游相依。

總結

以 `sys.objects` + `sp_depends` + GemBox.Spreadsheet 快速構建自動化相依文件產生器，解決大型舊系統「文件缺失、耦合不清、交接困難」痛點。核心在於**去重、排序、分類輸出**三步曲，代碼簡潔、可維護、可擴充。文件雖長 (6000+ 列)，但對重構、審計、新人上手價值極大。