SQL Server 系統預存程序 sp_pkeys / sp_keys 實戰：快速獲取資料表主鍵與外鍵中繼資料

前言

開發、文件化、自動化腳本中，常需程式化取得資料表的**主鍵 (PK)**、**外鍵 (FK)** 或**唯一條件約束** 中繼資料。SQL Server 提供系統預存程序 sp_pkeys (主鍵)、sp_fkeys (外鍵)、sp_keys (PK+FK 合併) 以及 ANSI 標準 INFORMATION_SCHEMA 檢視。本文詳細說明參數、回傳欄位、實戰範例與最佳實務。

一、sp_pkeys：取得主鍵資訊

語法

sp_pkeys 
    @table_name = 'TableName',      -- 必填，資料表名稱
    @table_owner = 'SchemaName',    -- 選填，預設 dbo
    @table_qualifier = 'DatabaseName' -- 選填，預設目前資料庫

回傳欄位

範例：Northwind.Orders 主鍵

USE Northwind;
GO
EXEC sp_pkeys @table_name = N'Orders', @table_owner = N'dbo', @table_qualifier = N'Northwind';
GO

二、sp_fkeys：取得外鍵資訊 (參考/被參考)

語法

sp_fkeys 
    @pktable_name = 'PK_Table',      -- 參考目標表 (被參考方)
    @pktable_owner = 'Schema',
    @pktable_qualifier = 'Database',
    @fktable_name = 'FK_Table',      -- 外鍵所在表 (參考方)
    @fktable_owner = 'Schema',
    @fktable_qualifier = 'Database'

若僅提供 @pktable_* → 回傳**參考該表的所有外鍵** (子表)。
若僅提供 @fktable_* → 回傳**該表參考的所有外鍵** (父表)。

回傳欄位 (關鍵)

三、sp_keys：主鍵 + 外鍵一次查 (較少用)

EXEC sp_keys @table_name = 'Orders', @table_owner = 'dbo', @table_qualifier = 'Northwind';

回傳欄位同 sp_pkeys，但會同時包含該表作為子表的外鍵資訊 (PKTABLE/FKTABLE 混合)。

四、ANSI 標準替代方案：INFORMATION_SCHEMA (推薦)

系統預存程序屬 Microsoft 專有，**INFORMATION_SCHEMA** 為 ANSI SQL 標準，跨資料庫平台可移植性更佳。

查詢主鍵

SELECT 
    TC.CONSTRAINT_CATALOG,  -- 資料庫
    TC.CONSTRAINT_SCHEMA,   -- 結構描述
    TC.TABLE_NAME,
    KCU.COLUMN_NAME,
    KCU.ORDINAL_POSITION,   -- KEY_SEQ 對應
    KCU.CONSTRAINT_NAME     -- PK_NAME 對應
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
  ON TC.TABLE_NAME = KCU.TABLE_NAME
 AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.TABLE_NAME = N'Orders'
  AND TC.CONSTRAINT_TYPE = N'PRIMARY KEY';

查詢外鍵 (含參考規則)

SELECT 
    RC.CONSTRAINT_CATALOG,
    RC.CONSTRAINT_SCHEMA,
    RC.TABLE_NAME AS FK_TABLE,
    KCU.COLUMN_NAME AS FK_COLUMN,
    RC.UNIQUE_CONSTRAINT_CATALOG AS PK_CATALOG,
    RC.UNIQUE_CONSTRAINT_SCHEMA AS PK_SCHEMA,
    RC.UNIQUE_CONSTRAINT_NAME AS PK_CONSTRAINT,
    CCU.TABLE_NAME AS PK_TABLE,
    CCU.COLUMN_NAME AS PK_COLUMN,
    RC.DELETE_RULE,
    RC.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
  ON RC.CONSTRAINT_CATALOG = KCU.CONSTRAINT_CATALOG
 AND RC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
 AND RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
  ON RC.UNIQUE_CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
 AND RC.UNIQUE_CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
 AND RC.UNIQUE_CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
WHERE KCU.TABLE_NAME = N'Order Details';  -- 子表

五、進階：sys 目錄檢視 (效能最佳、資訊最全)

生產環境自動化腳本建議直接查詢 sys.* 目錄檢視，避免 INFORMATION_SCHEMA 視圖的效能開銷與鎖定爭用。

主鍵 + 欄位細節

SELECT 
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    c.name AS ColumnName,
    ic.key_ordinal AS KeySeq,
    ic.is_descending_key,
    ic.is_included_column
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.is_primary_key = 1
  AND OBJECT_NAME(i.object_id) = 'Orders';

外鍵完整定義

SELECT 
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS FK_Schema,
    OBJECT_NAME(fk.parent_object_id) AS FK_Table,
    fk.name AS FK_Name,
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS FK_Column,
    OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS PK_Schema,
    OBJECT_NAME(fk.referenced_object_id) AS PK_Table,
    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS PK_Column,
    fk.delete_referential_action_desc AS DeleteRule,
    fk.update_referential_action_desc AS UpdateRule
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE OBJECT_NAME(fk.parent_object_id) = 'Order Details';

六、選型建議

總結

sp_pkeys、sp_fkeys、sp_keys 是 SQL Server 獨有的便捷工具，適合臨時查詢與簡單腳本。建立企業級中繼資料字典、自動化部署管線、跨平台工具時，請優先採用 INFORMATION_SCHEMA 或 sys.* 目錄檢視，確保標準相容與執行效能。