Ms Sqlserver与Oralce取得表的列信息与外键信息

Ms Sqlserver与Oralce取得表的列信息与外键信息  

2009-02-19 08:52:10|  分类： Oracle |字号 订阅

取得Ms SqlServer数据库中某张表的所有列：

 

SELECT sysobjects.name AS TABLENAME,
    syscolumns.NAME as NAME,
    systypes.name VTYPE,
    syscolumns.LENGTH,
    syscolumns.XPREC,
    syscolumns.XSCALE,
     isnullable,
     cdefault
 FROM syscolumns,sysobjects,systypes
 WHERE  syscolumns.id = sysobjects.id and
   syscolumns.xtype = systypes.xtype and
   sysobjects.type='U' and
   sysobjects.name = 表名

 

取得Oracle数据库某张表的所有列:

 

SELECT TNAME AS TABLENAME,
   CNAME AS NAME,
   COLTYPE AS VTYPE,
   WIDTH AS LENGTH,
   PRECISION AS XPREC,
   SCALE AS XSCALE,
   NULLS AS isnullable,
   DEFAULTVAL AS cdefault
 FROM COL
 WHERE TNAME = 表名;

 

 

取得Ms Sqlserver数据库的所有外键信息

 

select  
   b.name as key_name,
   a.name as name,
   d.name as f_name

  from   sysobjects   a   join   sysobjects   b   on   a.id=b.parent_obj  
  join   sysforeignkeys   c   on   b.id=c.constid  
  join   sysobjects   d   on   c.rkeyid=d.id  
  join   sysindexkeys   e   on   d.id=e.id  
  join   syscolumns   f   on   a.id=f.id   and   e.colid=f.colid  
  where     b.xtype='F'

 

取得Oracle数据库的所有外键信息：

 

select all_constraints.constraint_name as key_name,
   all_constraints.table_name as name ,
   all_indexes.table_name as f_name
from all_constraints,all_indexes
where  constraint_name like 'FK%'
and all_constraints.r_constraint_name = all_indexes.index_name