萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 獲取指定資料表主鍵欄位的兩種方法:系統預存程序 vs 系統表查詢

開發動態 SQL、代碼生成器、ORM 框架時,常需程式化取得資料表的主鍵欄位。SQL Server 提供系統預存程序 sp_pkeys 與系統目錄視圖 (sys.key_constraints, sys.index_columns) 兩種標準方式。本文對比兩法優劣並給出生產就緒查詢。

方法一:系統預存程序 sp_pkeys (最簡單、含資訊最全)

EXEC sp_pkeys @table_name = 'TableName', @table_owner = 'dbo', @table_qualifier = 'DatabaseName';

回傳結果集欄位:

欄位說明
TABLE_QUALIFIER資料庫名
TABLE_OWNERSchema 名
TABLE_NAME資料表名
COLUMN_NAME主鍵欄位名
KEY_SEQ欄位在主鍵中的順序 (1,2... 複合鍵)
PK_NAME主鍵約束名稱
  • 支援連結伺服器:EXEC [LinkedServer].master.dbo.sp_pkeys ...
  • ODBC/JDBC 標準介面: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;

舊版相容查詢 (SQL 2000, 僅供參考)

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

生產環境推薦:封裝為內嵌表值函數 (iTVF)

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 確保依賴物件不被誤刪。
  • 內嵌函數 (iTVF) 會被優化器**內展開**,零額外開銷,可直接 CROSS APPLY 批次處理多表。

擴展:同時取得主鍵與 Identity 欄位

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 比對工具開發將事半功倍。

copyright © 萬盛學電腦網 all rights reserved