MSSQL数据库应用系统开发中数据字典的研究应用

MSSQL数据库应用系统开发中数据字典的研究应用中国科学院计算机网络信息中心杜义华摘要灵活运用数据字典对于应用系统的开发实现、维护移植、文档管理都非常必要和实用。本文主要介绍数据字典的设计方法及其中与数据库系统相关信息的获取更新。关键词：数据字典系统表应用系统多离不开数据库，MSSQLServer2000在大小政企单位的信息管理、业务办公、内外网站系统中广泛应用。在系统开发过程中数据库结构可能时常改变，最初的设计定义常变得面目全非，需要及时更新数据库结构文档；经常需要略作调整后作另一个类似项目的快速开发基础，需要将通用性强的功能模块、数据表、代码保留，将不相关的功能和数据表清除；应用系统离不开查询汇总、编辑打印等，需要开发提供灵活强大的通过查询、通过统计、通过编辑功能等，因此，为保证系统底层应的柔性可扩展性，数据字典的设计应用非常重要。数据字典的设计应用系统的数据字典是一组自定义属性表，涉及应用系统中数据表、字段、功能模块、程序、代码等的定义和相关信息，可认为是“数据的数据”或“元数据”,主要方便系统的开发实现和升级维护。一般包括如下：1、信息集属性表(TabAttr)主要字段包括：表名、表描述、表类别、排列顺序、创建日期、修改日期、备注等。其中表名、表描述、创建日期、修改日期等均在数据库系统设计表时已定义；表类别用于对应用系统中可能很多表进行分类标识(如是程序用表、A类业务数据表、B类业务数据表)；排列顺序用于控制多个表的输出显示顺序，方便一次性到位的生成文档。部分结构如下：CREATETABLE[dbo].[TabAttr]([TabName][varchar](50),[TabDescr][varchar](50),[DispOrder][numeric](18,0),[TabType][varchar](50),[CreateDate][datetime],[LastModifyDate][datetime],[TabMemo][text])2、字段属性表(ColAttr)主要字段包括：对应表名、字段名、字段描述、字段类型、是否为自增长字段、是否为主关键词，是否可为空、对应代码表、备注、是否依据此查询、是否列表中显示、是否可编辑、是否用于排序等。其中前七项均在数据库设计时已定义；对应代码用于标注字段是否为代码型字段及对应的代码表，其它几项也是用于方便系统中通用编辑与通用查询。部分结构如下：CREATETABLE[dbo].[ColAttr]([TabName][varchar](50),[ColName][varchar](50),[ColDescr][varchar](80),[ColType][varchar](50),[ColDefault][varchar](50),[IsIdentity][char](1),[IsPrimaryKey][char](1),[IsAllowNull][char](1),[ColOrder][numeric](18,0),[IsQuery][char](1),[IsList][char](1),[ColCode][varchar](100),[IsSelect][char](1),[IsEdit][char](1),[IsOrder][char](1),[ColMemo][text])3、代码集属性表CodeAttr字段包括：代码表名、代码表描述、代码列名、代码长度、描述列名、描述长度、代码层次、第一层、第二层、第三层等的长度、代码类别、是否倒分层、代码值的正反顺序等。应用系统一般涉及各类代码表，按国家标准、行业标准等分类时一般有规范的正反层次关系，通过表、字段和代码集属性表，能方便的实现代码的下拉选择、分层查询及汇总。4、功能模块属性表(ModAttr)字段包括：模块ID、模块名称、模块说明、模块首页URL、安装情况、显示顺序等。用于对系统各功能模块管理，可配合角色权限管理、方便升级和移植。此外，还应有信息集类别表、有组织机构表、用户角色表、授权对照表、常用代码集表等。数据字典信息的维护更新数据字典的有些内容项，如是否对应代码、代码表的层次关系、功能模块信息等需在开发时录入或运行中管理，维护的工作量一般不大，手工维护即可；另有些信息如信息集和字段的名称、描述、字段类型、缺省值等，若手工录入，信息维护量大，而且可能时常变化、是重复工作，应与数据库系统实时刷新。以下主要讨论与MSSQL数据系统相关的数据字典信息获取和维护。1、MSSQL数据库架构信息分析MSSQLServer几乎将所有的配置信息、安全性信息和对象信息都存储在自身系统表中，可直接查询操作系统表或利用系统存储过程与系统函数、利用信息架构视图查询访问。应用系统开发中，常需要关注的信息有：1.1表和视图的名称信息可直接打开系统表sysobjects筛选或信息架构视图INFORMATION_SCHEMA.TABLES、INFORMATION_SCHEMA.VIEWS查看。常用的系统存储过程或函数有：sp_databases、sp_tables、OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY等。1.2表和视图中各列信息包括名称、数据类型、长度、精度、缺省值、描述、先后顺序等，信息分布在sysobjects、syscolumns、syscomments等系统表，通过信息架构视图INFORMATION_SCHEMA.COLUMNS、INFORMATION_SCHEMA.CHECK_CONSTRAINTS可查看。常用的系统存储过程或函数有：sp_columns、sp_sproc_columns、COLUMNPROPERTY、COL_LENGTH等。1.3表上定义的索引、主键/外键、触发器等信息信息分布在sysobjects、syscolumns、syscomments、sysconstraints、sysindexes、sysreference等系统表，可通过信息架构视图INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE、INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE、INFORMATION_SCHEMA.KEY_COLUMN_USAGE查看。常用的系统存储过程或函数有：sp_fkeys、sp_pkeys、COLUMNPROPERTY、OBJECTPROPERTY等。1.4表和列的扩展属性扩展属性可用于在数据库对象上定义和操作自定义的属性，如与企业管理器表设计视图中的表和列相关联的描述值，可以使用系统存储过程sp_addextendedproperty、sp_updateextendedproperty及sp_dropextendedproperty来管理，用fn_listextendedproperty()来检索。2、自动获取更新方法及源代码：数据字典中与数据库构架相关信息的自动维护是指能自动刷新为最新定义的的表和字段信息，同时不能误删改维护的相关的其它信息，方法是：补充新增的表和字段，删除已删的表和字段，将所有表和字段用系统表中最新的信息更新，包括表描述、列描述、缺省值、是否为主键、列顺序等。具体T-SQL实现时，需综合利用直接操作系统表、调用系统存储过程与系统函数、查询信息架构视图等方法，在获取表和字段的扩展属性、主键信息等时还需逐步扫描，存储过程完整的脚本如下：CREATEPROCEDUREGetTableInfoAS--1)将新增表名插入信息集属性表insertintoTabAttr(TabName)selecttable_namefromINFORMATION_SCHEMA.TABLESwheretable_namenotin(selectTabNamefromTabAttr)--2)从信息集属性表中删除已删的表名deletefromTabAttrwhereTabNamenotin(selecttable_namefromINFORMATION_SCHEMA.TABLES)--3)更新信息集的创建日期、修改日期updateTabAttrsetCreateDate=crdate,lastmodifydate=refdatefromTabAttrinnerjoinsysobjectsonTabName=name--4)从字段属性表中删除已删字段名deletefromColAttrwhereTabName+ColNamenotin(SELECTTABLE_NAME+COLUMN_NAMEfromINFORMATION_SCHEMA.COLUMNS)--5)将新增字段插入到字段属性表insertintoColAttr(TabName,ColName)SELECTTABLE_NAME,COLUMN_NAMEfromINFORMATION_SCHEMA.COLUMNSwhereTABLE_NAME+COLUMN_NAMEnotin(selectTabName+ColNamefromColAttr)--6)生成和更新字段的顺序、缺省值、是否允许为空、字段类型等信息updateColAttrsetColOrder=theColOrder,ColDefault=theColDefault,IsAllowNull=theIsAllowNull,ColType=theColTypefromColAttrinnerjoin(SELECTTABLE_NAME,COLUMN_NAME,theColOrder=ORDINAL_POSITION,theColDefault=isnull(left(right(COLUMN_DEFAULT,len(COLUMN_DEFAULT)-1),len(COLUMN_DEFAULT)-2),''),theIsAllowNull=(casewhenIS_NULLABLE='Yes'then'Y'else''end),theColType=(casewhen(xtype=34andxtype=58)orxtypein(61,98,99,104,127,189)thenDATA_TYPEwhenxtypein(59,60,106,108,122)thenDATA_TYPE+'('+convert(varchar,NUMERIC_PRECISION)+','+convert(varchar,NUMERIC_PRECISION_RADIX)+')'elseDATA_TYPE+'('+convert(varchar,CHARACTER_MAXIMUM_LENGTH)+')'end)fromINFORMATION_SCHEMA.COLUMNSinnerjoinsystypesonsystypes.name=DATA_TYPE)asmmonTABLE_NAME=TabNameandCOLUMN_NAME=ColName--7)更新字段的排序顺序、是否自动增加属性,直接从系统表中获取updateColAttrsetColOrder=cc,IsIdentity=ddfrom(SELECTaa=syscolumns.name,bb=sysobjects.name,cc=syscolumns.colorder,dd=(casewhensyscolumns.colstat=1then'Y'else''end)FROMsyscolumnsinnerjoinsysobjectsonsysobjects.id=syscolumns.idand(sysobjects.xtype='U'orsysobjects.xtype='V'))mmwhereColName=aaandTabName=bb--8)开始依次扫描各表，获取表和列的描述信息和主键信息--8.1)先创建存放主键信息的临时表CreateTable#TmpPkInfo(TABLE_QUALIFIERvarchar(50),TABLE_OWNERvarchar(50),TABL