IDC资讯中心
系统开发过中，数据字典表的更新
你们是否发现，
在系统开发过程中，你的数据库在不停的改变，最初发给每个开发人员的数据表定义，
已经变得面目全非了，如果你这个这个项目开发的负责人，下面的人老师说，怎么数据表又变了
怎么也不通知我们呢，有时候工作繁忙，也不一定能够通知到所有的开发人员。
为此，我特地作了一个自动化的工具，每当数据表又大的变动时，你不需要再次去修改原始数据字典文档了
你只要按照我的步骤来做，保证这一切在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, nuser, ndbo, ntable, @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, nuser, ndbo, ntable, @strname, ncolumn, 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. 如果还发现那些地方不符合你的要求，请自己完善
