依規範自動重命名所有外鍵：預存程序一鍵標準化

痛點：手工命名外鍵累人、不一致、易遺漏

PD 建模或 SSMS 設計表時，外鍵若不手工命名，系統自動產生類似 `FK__TableA__ColB__12345678` 的隨機名稱。時間久了，資料庫充斥不可讀的約束名，除錯、文件化、版本比對極其痛苦。

命名規範設計

fk__<外鍵表>__<外鍵欄位1>__<外鍵欄位2>...__<主鍵表>__<主鍵欄位1>__<主鍵欄位2>...

• 前綴 `fk__` 明確識別類型。
• 雙底線 `__` 分隔段落，單底線 `_` 保留欄位名內部底線。
• 多欄位複合鍵按 `KEY_SEQ` 順序串聯，確保唯一性。

範例：`fk__OrderDetail__OrderId__ProductId__Orders__OrderId__Products__ProductId`

完整預存程序：`proc_Rename_Fkeys`

CREATE PROC proc_Rename_Fkeys
AS
/*************************************************************************
功能：依規範重命名所有外鍵，清理重複外鍵定義
命名規則：fk__FkTable__FkCol(s)__PkTable__PkCol(s)
支援：多欄位複合外鍵、重複約束清理
**************************************************************************/
SET NOCOUNT ON;

DECLARE 
    @PkTable NVARCHAR(128), @FkTable NVARCHAR(128),
    @PkCols NVARCHAR(MAX), @FkCols NVARCHAR(MAX),
    @OldFkName NVARCHAR(128), @NewFkName NVARCHAR(MAX);

-- 1. 遍歷所有使用者資料表
DECLARE TableCur CURSOR LOCAL FAST_FORWARD FOR
    SELECT NAME FROM sys.objects WHERE TYPE = 'U';
OPEN TableCur;
FETCH NEXT FROM TableCur INTO @PkTable; -- 此處作為主鍵表視角

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 2. 取得當前表作為主鍵表的所有外鍵關聯 (sp_fkeys 回傳含重複列)
    CREATE TABLE #FKeysOut (
        OID INT IDENTITY,
        PKTABLE_NAME SYSNAME, PKCOLUMN_NAME SYSNAME,
        FKTABLE_NAME SYSNAME, FKCOLUMN_NAME SYSNAME,
        KEY_SEQ SMALLINT, FK_NAME SYSNAME
    );

    INSERT INTO #FKeysOut (PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_NAME, FKCOLUMN_NAME, KEY_SEQ, FK_NAME)
    EXEC sp_fkeys @PkTable;

    -- 3. 去重：同一組 (PK表, PK欄位, FK表, FK欄位, KEY_SEQ) 只保留最小 OID
    SELECT MIN(OID) AS OID INTO #Keep FROM #FKeysOut
    GROUP BY PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_NAME, FKCOLUMN_NAME, KEY_SEQ;

    -- 4. 識別重複外鍵 (同一定義但不同名稱)
    SELECT FKTABLE_NAME, FK_NAME, OID INTO #DropDup FROM #FKeysOut
    WHERE OID NOT IN (SELECT OID FROM #Keep);

    -- 5. 重命名保留的外鍵
    DECLARE RenameCur CURSOR LOCAL FOR
        SELECT PKTABLE_NAME, FKTABLE_NAME, FK_NAME 
        FROM #FKeysOut WHERE OID IN (SELECT OID FROM #Keep)
        GROUP BY PKTABLE_NAME, FKTABLE_NAME, FK_NAME;
    OPEN RenameCur;
    FETCH NEXT FROM RenameCur INTO @PkTable, @FkTable, @OldFkName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @PkCols = ''; SET @FkCols = '';
        SELECT @FkCols = @FkCols + '__' + FKCOLUMN_NAME,
               @PkCols = @PkCols + '__' + PKCOLUMN_NAME
        FROM #FKeysOut WHERE FK_NAME = @OldFkName ORDER BY KEY_SEQ;

        SET @NewFkName = 'fk__' + @FkTable + @FkCols + '__' + @PkTable + @PkCols;
        -- 避免名稱過長截斷 (sysname 128 字元)
        IF LEN(@NewFkName) > 128 SET @NewFkName = LEFT(@NewFkName, 125) + '...';
        
        EXEC sp_rename @OldFkName, @NewFkName, 'OBJECT';
        FETCH NEXT FROM RenameCur INTO @PkTable, @FkTable, @OldFkName;
    END
    CLOSE RenameCur; DEALLOCATE RenameCur;

    -- 6. 刪除重複外鍵定義
    DECLARE DropCur CURSOR LOCAL FOR
        SELECT FKTABLE_NAME, FK_NAME FROM #DropDup;
    OPEN DropCur;
    FETCH NEXT FROM DropCur INTO @FkTable, @OldFkName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC('ALTER TABLE ' + QUOTENAME(@FkTable) + ' DROP CONSTRAINT ' + QUOTENAME(@OldFkName));
        FETCH NEXT FROM DropCur INTO @FkTable, @OldFkName;
    END
    CLOSE DropCur; DEALLOCATE DropCur;

    -- 清理暫存表
    DROP TABLE #FKeysOut; DROP TABLE #Keep; DROP TABLE #DropDup;

    FETCH NEXT FROM TableCur INTO @PkTable;
END
CLOSE TableCur; DEALLOCATE TableCur;

執行與驗證

EXEC proc_Rename_Fkeys;
-- 驗證
SELECT name FROM sys.objects WHERE type = 'F' ORDER BY name;
-- 應全為 fk__ 開頭且可讀性大幅提升

擴充建議

• 加入 Schema 支援：`fk__Schema_FkTable__...` 避免跨 Schema 同名衝突。
• 長度截斷策略優化：改用雜湊後綴而非單純截斷，保證唯一性。
• 整合至 CI/CD**：部署管線中自動執行，確保命名規範持續合規。
• 擴展至其他物件：預設約束 (`df__`)、檢查約束 (`ck__`)、索引 (`ix__`)、觸發器 (`tr__`) 同理可自動化。

總結

一支預存程序，徹底解決外鍵命名混亂問題。核心技巧：`sp_fkeys` 取得關聯 → 分組去重 → 動態組裝標準名稱 → `sp_rename` 重命名 → `DROP CONSTRAINT` 清理重複。納入自動化流程，讓資料庫物件命名永遠保持整潔、可讀、專業。