Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1168308
  • 博文数量: 178
  • 博客积分: 2776
  • 博客等级: 少校
  • 技术积分: 2809
  • 用 户 组: 普通用户
  • 注册时间: 2012-03-22 15:36
文章分类

全部博文(178)

文章存档

2014年(3)

2013年(66)

2012年(109)

分类: Oracle

2012-06-04 10:54:03

  接上次内容,本文介绍MS SQLServer的数据库获取方法和Oracle数据库获取方法。

  先来介绍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   http://www.cuug.com/

  (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 @#

  --列信息

  select * from all_col_comments t

  where @#

  --主键、外键对照

  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME

  from all_constraints

  where @# and (@# or @#)

  --主键、外键信息

  select *

  from all_cons_columns

  where @#

  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);

  好了,这个数据字典多种数据库获取方法系列到此结束了,希望读者能从这系列的文章中找到想要的东西。

阅读(2386) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~