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 = '您的表格名称')