系統開發過中，數據字典表的更新
你們是否發現，
在系統開發過程中，你的數據庫在不停的改變，最初發給每個開發人員的數據表定義，
已經變得面目全非了，如果你這個這個項目開發的負責人，下面的人老師說，怎么數據表又變了
怎么也不通知我們呢，有時候工作繁忙，也不一定能夠通知到所有的開發人員。
為此，我特地作了一個自動化的工具，每當數據表又大的變動時，你不需要再次去修改原始數據字典文檔了
你只要按照我的步驟來做，保證這一切在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. 如果還發現那些地方不符合你的要求，請自己完善
