[表], [字段], [数据类型], [允许为空], [主键], [外键], [引用表], [说明描述]

SELECT TOP (100) PERCENT AS 表, AS 字段, AS 数据类型, CASE WHEN b.isnullable = 0 THEN '-' WHEN b.isnullable = 1 THEN '是' END AS 允许为空,
CASE WHEN d .name IS NULL THEN '-' WHEN d .name IS NOT NULL THEN '是' END AS 主键, CASE WHEN e.parent_object_id IS NULL THEN 0 ELSE 1 END AS 外键,
CASE WHEN e.parent_object_id IS NULL THEN '-' ELSE END AS 引用表, CASE WHEN h.value IS NULL THEN '-' ELSE h.value END AS 说明描述,
i.text AS 默认值
FROM sys.sysobjects AS a INNER JOIN
sys.syscolumns AS b ON = INNER JOIN
sys.systypes AS c ON b.xtype = c.xtype LEFT OUTER JOIN
(SELECT, sc.colid,
FROM sys.syscolumns AS sc INNER JOIN
sys.sysobjects AS so ON = INNER JOIN
sys.sysindexkeys AS si ON = AND sc.colid = si.colid
WHERE (si.indid = 1)) AS d ON = AND b.colid = d.colid LEFT OUTER JOIN
sys.foreign_key_columns AS e ON = e.parent_object_id AND b.colid = e.parent_column_id LEFT OUTER JOIN
sys.objects AS g ON e.referenced_object_id = g.object_id LEFT OUTER JOIN
sys.extended_properties AS h ON = h.major_id AND b.colid = h.minor_id LEFT OUTER JOIN
sys.syscomments AS i ON b.cdefault =
WHERE (a.type = 'U') AND ( <> 'sysname')
ORDER BY 表, 字段

还有一个查全库表字段的,参见另一篇 《通过(sys.sysobjects)对象表,查询全库表的基础信息Sql Server 查询全库表信息