經典 SQL 產生 SQL Server 資料字典：含表/欄位說明、主鍵、身分識別、預設值

一條經典查詢涵蓋資料字典核心欄位，可依專案需求調整：

SELECT
  sysobjects.name AS [table],
  sysproperties.[value] AS 表說明,
  syscolumns.name AS field,
  properties.[value] AS 字段說明,
  systypes.name AS type,
  syscolumns.length,
  ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0) AS 小數位數,
  syscolumns.isnullable AS isnull,
  CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS [Default],
  CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 標識,
  CASE WHEN EXISTS (
    SELECT 1 FROM sysobjects
    WHERE xtype = 'PK' AND name IN (
      SELECT name FROM sysindexes
      WHERE indid IN (
        SELECT indid FROM sysindexkeys
        WHERE id = syscolumns.id AND colid = syscolumns.colid
      )
    )
  ) THEN '√' ELSE '' END AS 主鍵
FROM syscolumns
INNER JOIN sysobjects ON sysobjects.id = syscolumns.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
LEFT OUTER JOIN sysproperties properties ON syscolumns.id = properties.id
  AND syscolumns.colid = properties.smallid
LEFT OUTER JOIN sysproperties ON sysobjects.id = sysproperties.id
  AND sysproperties.smallid = 0
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
WHERE sysobjects.xtype = 'U'

欄位對照表

調整建議

• 加入 精度：COLUMNPROPERTY(..., 'PRECISION')
• 加入 建立/修改時間：sysobjects.crdate/refdate
• SQL Server 2005+ 改用 sys.extended_properties 取代 sysproperties
• 輸出排序：ORDER BY sysobjects.name, syscolumns.colorder

此查詢為眾多資料字典產生器的基礎範本，理解其關聯邏輯可自行擴充客製化欄位。