CSDN博客

img xxj

系统开发过中,2分钟之内搞定数据字典表文档的更新

发表于2004/3/16 10:01:00  862人阅读

 

系统开发过中,数据字典表的更新

 

你们是否发现,

在系统开发过程中,你的数据库在不停的改变,最初发给每个开发人员的数据表定义,

已经变得面目全非了,如果你这个这个项目开发的负责人,下面的人老师说,怎么数据表又变了

怎么也不通知我们呢,有时候工作繁忙,也不一定能够通知到所有的开发人员。

为此,我特地作了一个自动化的工具,每当数据表又大的变动时,你不需要再次去修改原始数据字典文档了

你只要按照我的步骤来做,保证这一切在2分钟之内,帮你搞定数据字典文档的更新。

注意本方法,只针对MS SQL Server 2000。很遗憾,这一切,还没有做到完全自动化,谁有这个兴趣,完善它,我相信到时候,这一切会在1分钟之内搞定。

1.     在你的系统数据库中,创建以下存储过程

   该存储过程是获取数据字典信息

 

create Procedure GetAllTableInfo As

begin

       DECLARE @au_id varchar(11), @strName varchar(50), @Tid int,@strOwner varchar(50)

      

       --获取所有的用户表信息

       DECLARE All_Tables CURSOR

          FOR select convert(varchar,Name),ID from sysobjects where xtypE='U' order by Name

      

       Create Table #ColInfo

       (

              TName varchar(50),               --表名称

              ColName varchar(50),              --列名称

              REMARK sql_variant,              --注释

              Type varchar(50),              --数据类型

              DefaultValue varchar(255),   --缺省值

              IsAllowNull varchar(10),       --是否允许为空

              IsPrimaryKey char(2)           --是否为主键,用*标记

       )

       --主键信息

       Create Table #PkInfo

       (

              TABLE_QUALIFIER varchar(50),

              TABLE_OWNER varchar(50),

              TABLE_NAME varchar(50),

              COLUMN_NAME varchar(50),

              KEY_SEQ int,

              PK_NAME varchar(50)

       )

      

       set @strOwner=user_name()

      

       OPEN All_Tables

       FETCH NEXT FROM All_Tables into @strName,@Tid

       while @@FETCH_STATUS = 0

       begin

              --插入表及其注释信息

              Insert Into #ColInfo

              SELECT @StrName,'',isnull(Value,''),'','','',''

              FROM (select @strName TName) A left join

                     ::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, NULL, NULL)  --表的注释信息

                    ON 1=1

              --插入列信息

              insert into #ColInfo

              select @strName,C.name CName,convert(varchar,R.value)+(case c.status when 0x80 then char(13)+char(10)+'自动增长列' else '' end),

                     (case when (C.xtype>=34 and C.xtype<=58) or C.xtype in (61,98,99,104,127,189) then T.name when c.xtype in (59,60,106,108,122) then T.name+'('+convert(varchar,C.xprec)+','+convert(varchar,C.xscale)+')' else T.name+'('+convert(varchar,C.length)+')' end),

                     isnull(Def.text,''),

                     case isnullable when 1 then '' else '' end,

                     ''

              from (systypes T

                   inner join

                   (

                        syscolumns C

                        left join

                        SysComments Def

                        on C.cdefault=Def.id and 1=Def.colid

                   )

                   on T.xtype=C.xtype and T.xusertype=C.xusertype

                     )

                   left join

                     ::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, N'Column', NULL) R --列的注释信息

                   on convert(varchar,R.objname)=convert(varchar,C.Name)

              where C.id=object_id(@strName)

 

             

              insert into #PkInfo EXEC [dbo].[sp_pkeys] @strName,@strOwner, DEFAULT

             

              fetch next from All_Tables into @strName,@Tid

       end

      

       --更新主键信息

       update #ColInfo

        set IsPrimaryKey=''

        from #PkInfo

       where #PkInfo.TABLE_NAME=#ColInfo.TName and #PkInfo.COLUMN_NAME=#ColInfo.ColName

 

       Close All_Tables

       DEALLOCATE All_Tables

      

       select '%%%%' ObjName,'' REMARK,'' Col1,'' Col2,'' Col3,'' Col4,TName,0 flag

       from #ColInfo

       where ColName=''

       union

       select  '表名:'+TName,REMARK,'','','','',TName,1 flag

       from #ColInfo

       where ColName=''

       union

       select '列名','注释','数据类型','缺省值','是否允许空','主键',TName,2

       from #ColInfo

       where ColName=''

       union

       select ColName,REMARK,Type,DefaultValue,IsAllowNull,

              IsPrimaryKey,TName,3

       from #ColInfo

       where ColName<>''

      

       order by 7,8,3

 

       drop table #PkInfo

       drop table #ColInfo 

end

 

2.     MS SQL Sever 提供的查询分析器中,

Exec GetAllTableInfo

3.     将查询结果信息,复制到MS Excel Sheet

4.     MS Excel Sheet中,删除掉最后两列

5.     全选Sheet信息,复制到MS Word中,执行下面 Word宏命令

Sub Macro1()

'

' Macro1 Macro

' 宏在 2004-3-12 徐侠君 录制

'

A:

    Selection.Find.ClearFormatting

    With Selection.Find

        .Text = "%%%%"

        .Replacement.Text = ""

        .Forward = True

        .Wrap = wdFindContinue

        .Format = False

        .MatchCase = False

        .MatchWholeWord = False

        .MatchByte = True

        .MatchWildcards = False

        .MatchSoundsLike = False

        .MatchAllWordForms = False

    End With

    If Selection.Find.Execute Then

   

        Selection.Delete Unit:=wdCharacter, Count:=1

       

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

       

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

       

        Selection.Cells.Merge

        Selection.MoveDown Unit:=wdLine, Count:=1

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend

        Selection.Font.Bold = wdToggle

        Selection.MoveRight Unit:=wdCharacter, Count:=1

        Selection.SelectCell

        Selection.MoveRight Unit:=wdCharacter, Count:=4, Extend:=wdExtend

        Selection.Cells.Merge

        Selection.MoveDown Unit:=wdLine, Count:=1

        Selection.HomeKey Unit:=wdLine, Extend:=wdExtend

        Selection.MoveLeft Unit:=wdCharacter, Count:=2

        Selection.HomeKey Unit:=wdLine

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.EndKey Unit:=wdLine, Extend:=wdExtend

        Selection.Font.Bold = wdToggle

        Selection.MoveDown Unit:=wdLine, Count:=2

        GoTo A

    Else

        MsgBox "修改各是成功"

    End If

   

End Sub

 

1.     如果还发现那些地方不符合你的要求,请自己完善

0 0

相关博文

我的热门文章

img
取 消
img