Oracle 数据字典查询

1.查询表的相关信息

查询结果如下：

 查询脚本如下：

select tt1.owner,
       tt1.table_name,
       tt1.comments,
       tt1.last_analyzed,
       tt1.num_rows,
       tt2.colcnt
  from (select t1.owner,
               t1.table_name,
               c1.comments,
               t1.last_analyzed,
               t1.num_rows
          from all_tables t1, ALL_TAB_COMMENTS c1
         where t1.owner = c1.owner
           and t1.table_name = c1.table_name) tt1,
       
       (select t5.OWNER, t5.TABLE_NAME, count(1) as colcnt
          from dba_tab_columns t5
         where t5.OWNER not in
               ('SYS', 'SYSTEM', 'OUTLN', 'DIP', 'ORACLE_OCM', 'DBSNMP',
                'APPQOSSYS', 'WMSYS', 'EXFSYS', 'CTXSYS', 'XDB', 'ANONYMOUS',
                'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
                'MDSYS', 'OLAPSYS', 'MDDATA', 'SPATIAL_WFS_ADMIN_USR',
                'SPATIAL_CSW_ADMIN_USR', 'SYSMAN', 'MGMT_VIEW', 'FLOWS_FILES',
                'APEX_PUBLIC_USER', 'APEX_030200', 'OWBSYS', 'OWBSYS_AUDIT',
                'SCOTT', 'HR', 'OE', 'IX', 'SH', 'PM', 'BI', 'XS$NULL')
         group by t5.OWNER, t5.TABLE_NAME) tt2
 where tt1.owner = tt2.owner
   and tt1.table_name = tt2.table_name
 order by tt1.owner, tt1.table_name

　　

2.查询字段相关信息　

查询结果如下：

 

 查询脚本如下：

select ttt1.owner,
       ttt1.table_name,
       ttt1.column_name,
       ttt1.DATA_TYPE,
       ttt1.comments,
       (case ttt2.constraint_type
         when 'P' THEN
          'Y'
         ELSE
          'N'
       END) "是否主键",
       (case ttt3.constraint_type
         when 'R' THEN
          'Y'
         ELSE
          'N'
       END) "是否外键",
       (case ttt4.constraint_type
         when 'U' THEN
          'Y'
         ELSE
          'N'
       END) "是否唯一值",
       ttt1.NULLABLE
  from (select tt1.owner,
               tt1.table_name,
               tt1.column_name,
               tt1.DATA_TYPE,
               tt1.COLUMN_ID,
               tt2.comments,
               tt1.NULLABLE
          from (select t2.owner,
                       t2.table_name,
                       t2.column_name,
                       t2.DATA_TYPE,
                       t2.COLUMN_ID,
                       t2.NULLABLE
                  from all_tab_columns t2
                 where t2.owner not in
                       ('SYS', 'SYSTEM', 'OUTLN', 'DIP', 'ORACLE_OCM',
                        'DBSNMP', 'APPQOSSYS', 'WMSYS', 'EXFSYS', 'CTXSYS',
                        'XDB', 'ANONYMOUS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS',
                        'SI_INFORMTN_SCHEMA', 'MDSYS', 'OLAPSYS', 'MDDATA',
                        'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR',
                        'SYSMAN', 'MGMT_VIEW', 'FLOWS_FILES',
                        'APEX_PUBLIC_USER', 'APEX_030200', 'OWBSYS',
                        'OWBSYS_AUDIT', 'SCOTT', 'HR', 'OE', 'IX', 'SH', 'PM', 'BI',
                        'XS$NULL')) tt1,
               ALL_COL_COMMENTS tt2
         where tt1.owner = tt2.owner
           and tt1.table_name = tt2.table_name
           and tt1.column_name = tt2.column_name) ttt1
  left join

 (select col.owner, col.table_name, col.column_name, con.constraint_type
    from all_constraints con, all_cons_columns col
   where con.constraint_name = col.constraint_name
     and con.owner = col.owner
     and con.table_name = col.table_name
     and con.constraint_type = 'P') ttt2 on ttt1.owner = ttt2.owner
                                        and ttt1.table_name =
                                            ttt2.table_name
                                        and ttt1.column_name =
                                            ttt2.column_name
  left join

 (select col.owner, col.table_name, col.column_name, con.constraint_type
    from all_constraints con, all_cons_columns col
   where con.constraint_name = col.constraint_name
     and con.owner = col.owner
     and con.table_name = col.table_name
     and con.constraint_type = 'R') ttt3 on ttt1.owner = ttt3.owner
                                        and ttt1.table_name =
                                            ttt3.table_name
                                        and ttt1.column_name =
                                            ttt3.column_name
  left join

 (select col.owner, col.table_name, col.column_name, con.constraint_type
    from all_constraints con, all_cons_columns col
   where con.constraint_name = col.constraint_name
     and con.owner = col.owner
     and con.table_name = col.table_name
     and con.constraint_type = 'U') ttt4 on ttt1.owner = ttt4.owner
                                        and ttt1.table_name =
                                            ttt4.table_name
                                        and ttt1.column_name =
                                            ttt4.column_name
 order by ttt1.owner, ttt1.table_name, ttt1.COLUMN_ID