熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> MySQL >> 正文

如何獲取MSSQLServer Oracel Access數據字典信息

2013-11-23 20:56:27  來源: MySQL 

  表說明
  SELECT dbosysobjectsname AS TableName
  dbosysproperties[value] AS TableDesc
  FROM dbosysproperties INNER JOIN
  dbosysobjects ON dbosyspropertiesid = dbosysobjectsid
  WHERE (dbosyspropertiessmallid = )
  ORDER BY dbosysobjectsname
  
  字段說明
  SELECT dbosysobjectsname AS TableName lid
  dbosyscolumnsname AS ColName dbosysproperties[value] AS ColDesc FROM dbosysproperties INNER JOIN
  dbosysobjects ON dbosyspropertiesid = dbosysobjectsid INNER JOIN
  dbosyscolumns ON dbosysobjectsid = dbosyscolumnsid AND
  dbosyspropertiessmallid = lid
  ORDER BY dbosysobjectsname lid
  
  主鍵外鍵信息(簡化)
  select
  c_objname  as CONSTRAINT_NAME
  t_objname  as TABLE_NAME
  colname  as COLUMN_NAME
  case lid
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  when reffkey then
  end   as ORDINAL_POSITION
  from
  sysobjects c_obj
  sysobjects t_obj
  syscolumns col
  sysreferences ref
  where
  permissions(t_objid) !=
  and c_objxtype in (F )
  and t_objid = c_objparent_obj
  and t_objid = colid
  and lid  in
  (reffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkeyreffkey)
  and c_objid = nstid
  union
  select
  iname   as CONSTRAINT_NAME
  t_objname  as TABLE_NAME
  colname  as COLUMN_NAME
  vnumber  as ORDINAL_POSITION
  from
  sysobjects c_obj
  sysobjects t_obj
  syscolumns col
  masterdbospt_values v
  sysindexes i
  where
  permissions(t_objid) !=
  and c_objxtype in (UQ PK)
  and t_objid = c_objparent_obj
  and t_objxtype = U
  and t_objid = colid
  and colname = index_col(t_objnameiindidvnumber)
  and t_objid = iid
  and c_objname = iname
  and vnumber >
  and vnumber <= ikeycnt
  and vtype = P
  
  order by CONSTRAINT_NAME ORDINAL_POSITION
  
  主鍵外鍵對照(簡化)
  select
  fc_objname  as CONSTRAINT_NAME
  iname   as UNIQUE_CONSTRAINT_NAME
  from
  sysobjects fc_obj
  sysreferences r
  sysindexes i
  sysobjects pc_obj
  where
  permissions(fc_objparent_obj) !=
  and fc_objxtype = F
  and nstid = fc_objid
  and rrkeyid = iid
  and rrkeyindid = iindid
  and rrkeyid = pc_objid
  
   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 ADOIntpas
  //po: TableName
  //DBCon:TADOConnection
  /ds:TADODataSet
  
  表信息
  DBConOpenSchema(siTables VarArrayOf([Null Null Table]) EmptyParam ds);
  
  列信息
  DBConOpenSchema(siColumns VarArrayOf([Null Null po]) EmptyParam ds);
  
  主鍵
  DBConOpenSchema(siPrimaryKeys EmptyParam EmptyParam ds);
  
  主鍵外鍵對照
  DBConOpenSchema(siForeignKeys EmptyParam EmptyParam ds);
From:http://tw.wingwit.com/Article/program/MySQL/201311/29331.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.