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

发布时间：2020-12-12 15:32:21 所属栏目：MsSql教程来源：网络整理

导读：获取SQLServer 的最完整数据字典的SQL 语句 ? 其实网上已经流传了很多关于获取 SQLServer 的数据字典的版本，不过我相信这个应该是最全的了，本语句包括了表、字段、字段类型、字段长度、是否为空、是否递增字段、索引名称、索引的定位、索引类型、主键、外

获取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?
?THEN '' ELSE syscomments.text
?END AS 缺省值
CASE WHEN COLUMNPROPERTY(syscolumns.id,'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 ''ITPUBELSE 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?
?' 其他 '
END AS 索引类型,CASE WHEN EXIST
(SELECT?
? FROM sysobjects
WHERE xtype = 'PK' AND name?
?(SELECT name
???? FROM sysindexes
? WHERE ind
?? (SELECT indid
????? FROM sysindexkeys
????? WHERE id = syscolumns.id AND colid = syscolumns.colid)
?THEN ' √ ' ELSE ''
?END AS 主键, CASE WHEN sysforeignkeys.constid IS NULL ?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.idI
?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.
?LEFT OUTER JOIN sysindexkeys??????????????? -- 索引中的键或列的信息
? ON sysindexkeys.id = syscolumns ?AND sysindexkeys.colid = syscolumns.col LEFT OUTER JOIN sysindexes????????????????? -- 数据库索引表 ? ON sysindexes.id = sysindexkeys.id AND sysindexes.indid = sysindexkeys.indid LEFT OUTER JOIN sysforeignkeysI ? ON sysforeignkeys.fkeyid = syscolumns.id AND sysforeignkeys.fkey = syscolumns.colid WHERE (sysobjects.xtype = 'U') order by sysobjects.id,syscolumns.colid ?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!