開發動態 SQL、代碼生成器、ORM 框架時,常需程式化取得資料表的主鍵欄位。SQL Server 提供系統預存程序 sp_pkeys 與系統目錄視圖 (sys.key_constraints, sys.index_columns) 兩種標準方式。本文對比兩法優劣並給出生產就緒查詢。
EXEC sp_pkeys @table_name = 'TableName', @table_owner = 'dbo', @table_qualifier = 'DatabaseName';
回傳結果集欄位:
| 欄位 | 說明 |
|---|---|
| TABLE_QUALIFIER | 資料庫名 |
| TABLE_OWNER | Schema 名 |
| TABLE_NAME | 資料表名 |
| COLUMN_NAME | 主鍵欄位名 |
| KEY_SEQ | 欄位在主鍵中的順序 (1,2... 複合鍵) |
| PK_NAME | 主鍵約束名稱 |
EXEC [LinkedServer].master.dbo.sp_pkeys ...DatabaseMetaData.getPrimaryKeys() 底層即呼叫此 SP。**SQL Server 2005+ 推薦**:使用 sys.key_constraints + sys.index_columns + sys.columns。
SELECT
c.name AS ColumnName,
ic.key_ordinal AS KeyOrdinal,
kc.name AS PK_ConstraintName
FROM sys.key_constraints kc
JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE kc.type = 'PK'
AND kc.parent_object_id = OBJECT_ID('dbo.TableName')
ORDER BY ic.key_ordinal;
DECLARE @objid INT = OBJECT_ID('dbo.TableName');
SELECT col_name(@objid, k.colid) AS PK_Column
FROM sysobjects o
JOIN sysindexes i ON i.name = o.name
JOIN sysindexkeys k ON k.indid = i.indid AND k.id = @objid
WHERE o.xtype = 'PK' AND o.parent_obj = @objid AND k.id = @objid
ORDER BY k.keyno;
原文即使用此舊版寫法,利用 sysobjects(對象)、sysindexes(索引)、sysindexkeys(索引鍵) 三張系統表聯結。
| 維度 | sp_pkeys | 系統目錄視圖查詢 |
|---|---|---|
| 語法簡潔度 | ✅ 極簡 (一行) | 需 JOIN 多張視圖 |
| 回傳資訊豐富度 | 含約束名、Schema、DB、順序 | 可自訂 SELECT 列、易擴展 (如加入型別、是否 Identity) |
| 效能 | 內部同樣查系統表,差異極小 | 可加 NOLOCK、精確控制計畫 |
| 可程式化嵌入 | 需 INSERT INTO #t EXEC sp_pkeys 暫存表 | **直接嵌入 SELECT/CTV/子查詢**,極適合動態 SQL 生成 |
| 跨版本相容 | 全版本支援 | 2005+ 標準視圖穩定;2000 需用舊系統表 |
| 權限需求 | 需 VIEW DEFINITION / 權限 | 需 VIEW DEFINITION / SELECT ON sys.objects |
CREATE FUNCTION dbo.fn_GetPrimaryKeyColumns (@TableName SYSNAME, @SchemaName SYSNAME = 'dbo')
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT
c.name AS ColumnName,
ic.key_ordinal AS KeyOrdinal,
kc.name AS PK_ConstraintName
FROM sys.key_constraints kc
JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE kc.type = 'PK'
AND kc.parent_object_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName))
AND SCHEMA_NAME(kc.schema_id) = @SchemaName;
GO
-- 使用
SELECT * FROM dbo.fn_GetPrimaryKeyColumns('Orders', 'dbo');
SCHEMABINDING 確保依賴物件不被誤刪。CROSS APPLY 批次處理多表。SELECT
c.name AS ColumnName,
ic.key_ordinal AS KeyOrdinal,
kc.name AS PK_ConstraintName,
c.is_identity AS IsIdentity,
IDENT_SEED(c.object_id) AS IdentitySeed,
IDENT_INCR(c.object_id) AS IdentityIncrement
FROM sys.key_constraints kc
JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE kc.type = 'PK'
AND kc.parent_object_id = OBJECT_ID('dbo.Orders');
掌握這兩種方法,動態 SQL、程式碼生成器、Schema 比對工具開發將事半功倍。