获取SQL Server数据字典的经典SQL语句

SELECT sysobjects.name AS [table], sysproperties.[value] AS 表说明,

syscolumns.name AS field, properties.[value] AS 字段说明, systypes.name AS type,

syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,

'Scale'), 0) AS 小数位数, syscolumns.isnullable AS isnull,

CASE WHEN syscomments.text IS NULL

THEN '' ELSE syscomments.text END AS [Default],

CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')

= 1 THEN '√' ELSE '' END AS 标识, 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 = syscolumns.id AND colid = syscolumns.colid)))

THEN '√' ELSE '' END AS 主键

FROM syscolumns INNER JOIN

sysobjects ON sysobjects.id = syscolumns.id INNER JOIN

systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN

sysproperties properties ON syscolumns.id = properties.id AND

syscolumns.colid = properties.smallid LEFT OUTER JOIN

sysproperties ON sysobjects.id = sysproperties.id AND

sysproperties.smallid = 0 LEFT OUTER JOIN

syscomments ON syscolumns.cdefault = syscomments.id

WHERE (sysobjects.xtype = 'U') order by [table]

转载于:https://www.cnblogs.com/fhuafeng/archive/2009/08/18/1548840.html