获取SQLServer的最完整数据字典的SQL语句

获取SQLServer的最完整数据字典的SQL语句
 
其实网上已经流传了很多关于获取SQLServer的数据字典的版本，不过我相信这个应该是最全的了，本语句包括了表、字段、字段类型、字段长度、是否为空、是否递增字段、索引名称、索引的定位、索引类型、主键、外键等；通过合理的裁剪可以很方便的生成相应的数据字典。

  SELECT 
sysobjects.name AS 表名称,
--sysproperties.[value] AS 表说明,
syscolumns.name AS 字段名称,
--properties.[value] AS 字段说明,
systypes.name AS 字段类型,
syscolumns.length AS 字段长度,
ISNULL(COLUMNPROPERTY(syscolumns.id,  syscolumns.name,'Scale'), 0) AS 小数位数,
                                                                                                                                      CASE WHEN syscolumns.isnullable=0

THEN ''
ELSE '√'
END AS 是否为空,
CASE WHEN syscomments.text IS NULL

THEN '' ELSE syscomments.text
END AS 缺省值,
CASE WHEN COLUMNPROPERTY(syscolumns.id,  syscolumns.name, 'IsIdentity')= 1

THEN '√' ELSE ''
END AS 递增字段,
CASE WHEN sysindexes.name IS NULL

THEN ''
ELSE sysindexes.name
END AS 索引名称,
CASE WHEN sysindexkeys.keyno IS NULL

THEN ''
ELSE  CONVERT(VARCHAR(10),sysindexkeys.keyno )
END AS 索引位置,
CASE WHEN sysindexes.indid=1

THEN '聚集索引'
WHEN sysindexes.indid>1 AND sysindexes.indid<>255

THEN '非聚集索引'
WHEN sysindexes.indid IS NULL

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 主键,
CASE WHEN sysforeignkeys.constid IS NULL

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
LEFT OUTER JOIN sysindexkeys
--索引中的键或列的信息

ON sysindexkeys.id = syscolumns.id

AND sysindexkeys.colid = syscolumns.colid
LEFT OUTER JOIN sysindexes
--数据库索引表

ON sysindexes.id = sysindexkeys.id

AND sysindexes.indid = sysindexkeys.indid
LEFT OUTER JOIN sysforeignkeys

ON sysforeignkeys.fkeyid = syscolumns.id

AND sysforeignkeys.fkey = syscolumns.colid
WHERE (sysobjects.xtype = 'U')
order by sysobjects.id,syscolumns.colid