為資料庫所有表批量新增主鍵 (PK) 腳本

應用場景

資料匯入完成後發現忘記建立主鍵，且所有表皆有 [ID] INT 欄位適合做 PK。

預存程式碼

CREATE PROCEDURE [TOOL].[sp_DB_AddPK]
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @vTable VARCHAR(100), @vSchema VARCHAR(100), @vSql VARCHAR(MAX);
    
    DECLARE cur_ CURSOR FOR
        SELECT [name], SCHEMA_NAME(schema_id)
        FROM sys.objects
        WHERE type IN (N'U')
        ORDER BY schema_id, name;
    
    OPEN cur_;
    FETCH NEXT FROM cur_ INTO @vTable, @vSchema;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 使用佔位符替換法，提升可讀性
        SET @vSQL = N'
            ALTER TABLE [#Schema#].[#Table#] 
            ADD CONSTRAINT [PK_#Table#] PRIMARY KEY CLUSTERED 
            ( [ID] ASC )
            WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF) 
            ON [PRIMARY];
            GO';
        
        SET @vSQL = REPLACE(@vSQL, '#Table#', @vTable);
        SET @vSQL = REPLACE(@vSQL, '#Schema#', @vSchema);
        
        PRINT @vSql;  -- 產生腳本供審查
        -- EXEC (@vSql);  -- 確認無誤後取消註解執行
        
        FETCH NEXT FROM cur_ INTO @vTable, @vSchema;
    END
    
    CLOSE cur_;
    DEALLOCATE cur_;
END

關鍵設計點

執行前檢查清單

1. 確認所有表確實有 [ID] 欄位且資料唯一
2. 檢查是否已存在 PK (腳本會報錯)
3. 評估 IGNORE_DUP_KEY 行為是否符合業務預期
4. 大表建立 PK 會鎖表，安排維護時段
5. 備份資料庫！

擴充版本：支援自訂 PK 欄位

-- 修改游標查詢，關聯 sys.columns 取得身分識別欄或第一個 NOT NULL 欄位
SELECT o.name, SCHEMA_NAME(o.schema_id), c.name AS pk_col
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.type = 'U' AND c.is_identity = 1
   OR (c.is_nullable = 0 AND c.column_id = 1)
-- 後續動態 SQL 使用 @pk_col 替換固定的 [ID]