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

最新推荐文章于 2021-08-08 14:21:20 发布

cxzhq2002

最新推荐文章于 2021-08-08 14:21:20 发布

阅读量839

点赞数

文章标签：存储 table join null permissions insert

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

你们是否发现，

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

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

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

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

你只要按照我的步骤来做，保证这一切在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 由徐侠君录制

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.Cells.Merge

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

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.Font.Bold = wdToggle

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

GoTo A

Else

MsgBox "修改各是成功"

End If

End Sub

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

========================================

----------------------------------------------
--------------- MS SQLServer -----------------
----------------------------------------------

--表说明
SELECT dbo.sysobjects.name AS TableName,
      dbo.sysproperties.[value] AS TableDesc
FROM dbo.sysproperties INNER JOIN
      dbo.sysobjects ON dbo.sysproperties.id = dbo.sysobjects.id
WHERE (dbo.sysproperties.smallid = 0)
ORDER BY dbo.sysobjects.name

--字段说明
SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.colid,
      dbo.syscolumns.name AS ColName, dbo.sysproperties.[value] AS ColDesc
FROM dbo.sysproperties INNER JOIN
      dbo.sysobjects ON dbo.sysproperties.id = dbo.sysobjects.id INNER JOIN
      dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id AND
      dbo.sysproperties.smallid = dbo.syscolumns.colid
ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid

--主键、外键信息(简化)
select
c_obj.name    as CONSTRAINT_NAME
,t_obj.name    as TABLE_NAME
,col.name    as COLUMN_NAME
,case col.colid
  when ref.fkey1 then 1
  when ref.fkey2 then 2
  when ref.fkey3 then 3
  when ref.fkey4 then 4
  when ref.fkey5 then 5
  when ref.fkey6 then 6
  when ref.fkey7 then 7
  when ref.fkey8 then 8
  when ref.fkey9 then 9
  when ref.fkey10 then 10
  when ref.fkey11 then 11
  when ref.fkey12 then 12
  when ref.fkey13 then 13
  when ref.fkey14 then 14
  when ref.fkey15 then 15
  when ref.fkey16 then 16
end      as ORDINAL_POSITION
from
sysobjects c_obj
,sysobjects t_obj
,syscolumns col
,sysreferences ref
where
permissions(t_obj.id) != 0
and c_obj.xtype in ('F ')
and t_obj.id = c_obj.parent_obj
and t_obj.id = col.id
and col.colid   in
(ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6,
ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12,
ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16)
and c_obj.id = ref.constid
union
select
i.name     as CONSTRAINT_NAME
,t_obj.name    as TABLE_NAME
,col.name    as COLUMN_NAME
,v.number    as ORDINAL_POSITION
from
sysobjects  c_obj
,sysobjects  t_obj
,syscolumns  col
,master.dbo.spt_values v
,sysindexes  i
where
permissions(t_obj.id) != 0
and c_obj.xtype in ('UQ' ,'PK')
and t_obj.id = c_obj.parent_obj
and t_obj.xtype = 'U'
and t_obj.id = col.id
and col.name = index_col(t_obj.name,i.indid,v.number)
and t_obj.id = i.id
and c_obj.name = i.name
and v.number > 0
  and v.number <= i.keycnt
  and v.type = 'P'

order by CONSTRAINT_NAME, ORDINAL_POSITION

--主键、外键对照(简化)
select
fc_obj.name   as CONSTRAINT_NAME
,i.name     as UNIQUE_CONSTRAINT_NAME
from
sysobjects fc_obj
,sysreferences r
,sysindexes i
,sysobjects pc_obj
where
permissions(fc_obj.parent_obj) != 0
and fc_obj.xtype = 'F'
and r.constid  = fc_obj.id
and r.rkeyid  = i.id
and r.rkeyindid = i.indid
and r.rkeyid  = pc_obj.id

----------------------------------------------
------------------- Oracle -------------------
----------------------------------------------

--表信息
select * from all_tab_comments t
where owner='DBO'

--列信息
select * from all_col_comments t
where owner='DBO'

--主键、外键对照
select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME
from all_constraints
where owner='DBO' and (Constraint_Type='P' or Constraint_Type='R')

--主键、外键信息
select *
from all_cons_columns
where owner='DBO'
order by Constraint_Name, Position

----------------------------------------------
------------------- Access -------------------
----------------------------------------------
//Access中的系统表MSysobjects存储属性的字段是二进制格式，不能直接分析
//可以采用ADO自带的OpenSchema方法获得相关信息

//use ADOInt.pas
//po: TableName
//DBCon:TADOConnection
/ds:TADODataSet

--表信息
DBCon.OpenSchema(siTables, VarArrayOf([Null, Null, 'Table']), EmptyParam, ds);

--列信息
DBCon.OpenSchema(siColumns, VarArrayOf([Null, Null, 'po']), EmptyParam, ds);

--主键
DBCon.OpenSchema(siPrimaryKeys, EmptyParam, EmptyParam, ds);

--主键、外键对照
DBCon.OpenSchema(siForeignKeys, EmptyParam, EmptyParam, ds);