SQL Server 查詢所有資料表與檢視結構的完整指令

概述

本文提供 SQL Server 2000 與 2005 兩個版本，用於取得指定資料庫中所有資料表或檢視的詳細欄位資訊（欄位名稱、資料型別、長度、說明、預設值、是否允許 NULL 等）。兩版本差異僅在於取得欄位說明的系統表不同。

SQL Server 2000 版本

SELECT 
  Sysobjects.name AS TABLE_NAME,
  syscolumns.Id,
  syscolumns.name AS COLUMN_NAME,
  systypes.name AS DATA_TYPE,
  syscolumns.length AS CHARACTER_MAXIMUM_LENGTH,
  sysproperties.[value] AS COLUMN_DESCRIPTION,
  syscomments.text AS COLUMN_DEFAULT,
  syscolumns.isnullable AS IS_NULLABLE
FROM syscolumns
INNER JOIN systypes
  ON syscolumns.xtype = systypes.xtype
LEFT JOIN sysobjects
  ON syscolumns.id = sysobjects.id
LEFT OUTER JOIN sysproperties
  ON (sysproperties.smallid = syscolumns.colid
      AND sysproperties.id = syscolumns.id)
LEFT OUTER JOIN syscomments
  ON syscolumns.cdefault = syscomments.id
WHERE syscolumns.id IN (
  SELECT id FROM SYSOBJECTS WHERE xtype = 'U'
)
AND (systypes.name <> 'sysname')
ORDER BY syscolumns.colid;

SQL Server 2005 版本

SELECT 
  Sysobjects.name AS TABLE_NAME,
  syscolumns.Id,
  syscolumns.name AS COLUMN_NAME,
  systypes.name AS DATA_TYPE,
  syscolumns.length AS CHARACTER_MAXIMUM_LENGTH,
  sys.extended_properties.[value] AS COLUMN_DESCRIPTION,
  syscomments.text AS COLUMN_DEFAULT,
  syscolumns.isnullable AS IS_NULLABLE
FROM syscolumns
INNER JOIN systypes
  ON syscolumns.xtype = systypes.xtype
LEFT JOIN sysobjects
  ON syscolumns.id = sysobjects.id
LEFT OUTER JOIN sys.extended_properties
  ON (sys.extended_properties.minor_id = syscolumns.colid
      AND sys.extended_properties.major_id = syscolumns.id)
LEFT OUTER JOIN syscomments
  ON syscolumns.cdefault = syscomments.id
WHERE syscolumns.id IN (
  SELECT id FROM SYSOBJECTS WHERE xtype = 'U'
)
AND (systypes.name <> 'sysname')
ORDER BY syscolumns.colid;

使用說明

• 將查詢中的 xtype = 'U' 改為 'V' 即可取得檢視資訊。
• 欄位說明來源：SQL 2000 使用 sysproperties，SQL 2005 使用 sys.extended_properties。
• 結果包含：資料表名、欄位 ID、欄位名、資料型別、最大長度、欄位說明、預設值、是否允許 NULL。

參考來源

DevX Tip 31235