SQL Server查詢資料庫欄位資訊

以下代碼相容SQL Server 2000。

SELECT

c.[name] AS '列名',

CASE

WHEN t.[name] = 'DECIMAL' OR t.[name] = 'NUMERIC'

THEN UPPER(t.[name]) + '(' + CAST(c.[xprec] AS VARCHAR) + ', ' + CAST(c.[xscale] AS VARCHAR) + ')'

WHEN t.[name] = 'BINARY' OR t.[name] = 'CHAR' OR t.[name] = 'NCHAR' OR t.[name] = 'NVARCHAR' OR t.[name] = 'VARBINARY' OR t.[name] = 'VARCHAR'

THEN UPPER(t.[name]) + '(' + CAST(c.[length] AS VARCHAR) + ')'

ELSE UPPER(t.[name])

END AS '資料',

CASE c.isnullable

WHEN 0 THEN '否'

WHEN 1 THEN '是'

END AS '可為空白',

ISNULL(p.value, '') AS '說明'

FROM SysColumns AS c LEFT JOIN

SysTypes AS t ON c.xtype = t.xtype LEFT JOIN

SysProperties AS p ON c.id = p.id AND c.colid = p.smallid

WHERE c.id = (SELECT id FROM SysObjects WHERE name = '您的表格名稱')