利用 INFORMATION_SCHEMA 系統視圖查詢 SQL Server 表主外鍵完整資訊

核心視圖

• INFORMATION_SCHEMA.COLUMNS：欄位基本屬性
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE：鍵欄位對應
• INFORMATION_SCHEMA.TABLE_CONSTRAINTS：約束類型（PK/FK）
• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS：外鍵引用關係

遞進式查詢建構

1. 基礎欄位資訊：整合 DATA_TYPE、CHARACTER_MAXIMUM_LENGTH、NUMERIC_PRECISION/SCALE 產生完整型別字串（如 VARCHAR(50)、DECIMAL(10,2)）。
2. 標記主鍵/外鍵：LEFT JOIN KEY_COLUMN_USAGE + TABLE_CONSTRAINTS，依 CONSTRAINT_TYPE 判斷 PK/FK。
3. 外鍵引用表/欄位：再 JOIN REFERENTIAL_CONSTRAINTS 取得 UNIQUE_CONSTRAINT_SCHEMA/NAME，再 JOIN KEY_COLUMN_USAGE 解析被引用表與欄位。
4. 美化輸出：用 CASE WHEN ORDINAL_POSITION=1 只顯示一次表名；ISNULL 將 NULL 顯示為空串；√ 標記 PK/FK。

最終查詢範本

SELECT
  CASE WHEN c.ORDINAL_POSITION = 1 THEN c.TABLE_SCHEMA + '.' + c.TABLE_NAME ELSE '' END AS TABLE_NAME,
  c.COLUMN_NAME,
  -- 型別格式化 CASE WHEN ...
  ISNULL(c.COLUMN_DEFAULT, '') AS COLUMN_DEFAULT,
  CASE WHEN c.IS_NULLABLE = 'YES' THEN '√' ELSE '' END AS IS_NULLABLE,
  CASE WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN '√' ELSE '' END AS IS_PRIMARY_KEY,
  CASE WHEN tc.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN '√' ELSE '' END AS IS_FOREIGN_KEY,
  ISNULL(fkcu.COLUMN_NAME, '') AS FOREIGN_KEY,
  ISNULL(fkcu.TABLE_NAME, '') AS FOREIGN_TABLE
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
  ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
  AND kcu.TABLE_NAME = c.TABLE_NAME
  AND kcu.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
  ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
  AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fc
  ON kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA
  AND kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcu
  ON fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA
  AND fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME
WHERE c.TABLE_NAME = 'Address'
ORDER BY c.ORDINAL_POSITION;

最佳實踐建議

• 優先使用 INFORMATION_SCHEMA 標準視圖，避免依賴 sys.* 內部表（版本相容性差）。
• 查詢擴充屬性時務必加 class_desc = 'OBJECT_OR_COLUMN' 過濾，避免重複行。
• 支援 SQL Server 2005+，新版型別（如 DATE、DATETIME2）自動相容。