获取MS SQL库数据字典的经典SQL语句

1. SELECT sysobjects.name AS [table], sysproperties.[value] AS 表说明,
2.  syscolumns.name AS field, properties.[value] AS 字段说明, systypes.name AS type,
3.  syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,
4.  'Scale'), 0) AS 小数位数, syscolumns.isnullable AS isnull,
5.  CASE WHEN syscomments.text IS NULL
6.  THEN '' ELSE syscomments.text END AS [Default],
7.  CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')
8. = 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS
9.  (SELECT 1
10.  FROM sysobjects
11.  WHERE xtype = 'PK' AND name IN
12.  (SELECT name
13.  FROM sysindexes
14.  WHERE indid IN
15.  (SELECT indid
16.  FROM sysindexkeys
17.  WHERE id = syscolumns.id AND colid = syscolumns.colid)))
18.  THEN '√' ELSE '' END AS 主键
19.  FROM syscolumns INNER JOIN
20.  sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
21.  systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
22.  sysproperties properties ON syscolumns.id = properties.id AND
23.  syscolumns.colid = properties.smallid LEFT OUTER JOIN
24.  sysproperties ON sysobjects.id = sysproperties.id AND
25.  sysproperties.smallid = 0 LEFT OUTER JOIN
26.  syscomments ON syscolumns.cdefault = syscomments.id
27.  WHERE (sysobjects.xtype = 'U')