SQL Server 表結構查詢進階：修正 Bug、支援新型別與 INFORMATION_SCHEMA 最佳實踐

舊版 sys 視圖查詢的缺陷

• 缺少 g.class_desc = 'OBJECT_OR_COLUMN' 過濾，導致擴充屬性重複行。
• 嵌套子查詢過多，效能差；不支援 SQL Server 2012 新型別（DATE、DATETIME2、DATETIMEOFFSET 等）。
• 型別顯示無長度資訊（如只顯示 varchar 而非 varchar(50)）。

修正後的 sys 視圖查詢關鍵點

LEFT JOIN sys.extended_properties g
  ON a.id = g.major_id
  AND a.colid = g.minor_id
  AND g.class_desc = 'OBJECT_OR_COLUMN'  -- 關鍵過濾

推薦：基於 INFORMATION_SCHEMA 的現代查詢

結合 INFORMATION_SCHEMA.COLUMNS、sys.columns（取 IS_IDENTITY）、sys.objects（PK）、sys.foreign_key_columns（FK）與擴充屬性，單次查詢輸出：

• 表名（僅首行顯示）、欄位名、完整型別（含長度/精度）
• 身分欄、可空、預設值、欄位/表說明
• PK/FK 標記、FK 引用表與欄位

核心查詢片段

SELECT
  CASE WHEN a.ORDINAL_POSITION = 1 THEN a.TABLE_SCHEMA + '.' + a.TABLE_NAME ELSE '' END AS TABLE_NAME,
  a.COLUMN_NAME,
  -- 型別格式化邏輯同前文
  CASE WHEN c.IS_IDENTITY = 1 THEN 'YES' ELSE 'NO' END AS IS_IDENTITY,
  a.IS_NULLABLE,
  ISNULL(a.COLUMN_DEFAULT, '') AS Default_Value,
  ISNULL(p.value, '') AS COLUMN_Description,
  CASE WHEN o.name IS NULL THEN '' ELSE '√' END AS Is_PrimaryKey,
  CASE WHEN f.parent_column_id IS NULL THEN '' ELSE '√' END AS Is_Foreignkeys,
  CASE WHEN f.referenced_object_id IS NULL THEN '' ELSE OBJECT_NAME(f.referenced_object_id) END AS Foreign_Table,
  CASE WHEN f.referenced_object_id IS NULL THEN '' ELSE (
    SELECT name FROM sys.columns
    WHERE object_id = f.referenced_object_id AND column_id = f.referenced_column_id
  ) END AS Foreign_key
FROM INFORMATION_SCHEMA.COLUMNS a
INNER JOIN sys.columns c
  ON OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) = c.OBJECT_ID
  AND a.COLUMN_NAME = c.NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b
  ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
  AND a.TABLE_NAME = b.TABLE_NAME
  AND a.COLUMN_NAME = b.COLUMN_NAME
LEFT JOIN sys.sysobjects o
  ON o.name = b.CONSTRAINT_NAME AND o.xtype = 'PK'
LEFT JOIN sys.extended_properties p
  ON OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) = p.major_id
  AND a.ORDINAL_POSITION = p.minor_id
  AND p.class_desc = 'OBJECT_OR_COLUMN'
LEFT JOIN sys.foreign_key_columns f
  ON OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) = f.parent_object_id
  AND a.ORDINAL_POSITION = f.parent_column_id
WHERE a.TABLE_NAME = 'Address'
ORDER BY a.TABLE_SCHEMA, a.TABLE_NAME, a.ORDINAL_POSITION;

總結

• 棄用 syscolumns、sysobjects 等舊式目錄視圖。
• 善用 INFORMATION_SCHEMA 標準視圖 + 少量 sys.* 補強（身分欄、FK）。
• 統一加上 class_desc 過濾，避免擴充屬性重複。