SQL Server 獲取數據表結構完整信息的系統查詢腳本

單條查詢返回表名、表說明、列序號、列名、標識列、主鍵、數據類型、佔用字節、精度、小數位、允許空、默認值、列說明——適合文檔生成、元數據同步、架構審查。

SELECT
  表名 = CASE WHEN a.colorder = 1 THEN d.name ELSE '' END,
  表說明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '') ELSE '' END,
  字段序號 = a.colorder,
  字段名 = a.name,
  標識 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END,
  主鍵 = 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 = a.id AND colid = a.colid
      )
    )
  ) THEN '√' ELSE '' END,
  類型 = b.name,
  佔用字節數 = a.length,
  精度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
  小數位數 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
  允許空 = CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END,
  默認值 = ISNULL(e.text, ''),
  字段說明 = ISNULL(g.[value], '')
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sysproperties g ON a.id = g.id AND a.colid = g.smallid
LEFT JOIN sysproperties f ON d.id = f.id AND f.smallid = 0
-- WHERE d.name = '目標表名'  -- 單表查詢時取消註釋
ORDER BY a.id, a.colorder

關鍵系統表/函數說明

• syscolumns：列定義核心表
• systypes：類型系統表
• sysobjects：對象目錄（xtype='U' 為用戶表）
• sysindexes/indexkeys：索引鍵關係，用於推導主鍵
• COLUMNPROPERTY()：獲取標識、精度、小數位等擴展屬性
• sysproperties：擴展屬性存儲（sp_addextendedproperty 寫入）

SQL Server 2005+ 建議改用 sys.columns、sys.tables、sys.extended_properties 等目錄視圖，邏輯更清晰、性能更優。