《数据字典组成及导出语句》由会员分享，可在线阅读，更多相关《数据字典组成及导出语句（3页珍藏版）》请在金锄头文库上搜索。
1、数据字典的组成：1、数据项2、数据结构3、数据流4、数据存储5、处理过程本语句包括了表、字段、 字段类型、 字段长度、 是否为空、 是否递增字段、 索引名称、索引的定位、 索引类型、 主键、外键等；通过合理的裁剪可以很方便的生成相应的数据字典。SELECT sysobjects.name AS 表名称 , -sysproperties.value AS 表说明 , syscolumns.name AS 字段名称 , -properties.value AS 字段说明 , systypes.name AS 字段类型 , syscolumns.length AS 字段长度 , ISNULL(COL
2、UMNPROPERTY(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 WHE
3、N 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.indid1 AND sysindexes.indid255 THEN 非聚集索引 WHEN sysindexes.indid IS NULL THEN
4、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 EN
5、D 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 syspro
6、perties - 表属性信息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
