5.1列出已创建的表的清单

select * from all_tables ;
select * from dba_tables ;
select * from user_tables ;
  

 

  
1
  
2
  
3
 

5.2 列出表的列

select * from all_tab_columns a ;
select * from dba_tab_columns a ;
select * from user_tab_columns a ;
  

 

  
1
  
2
  
3
 

5.3列出表的索引列

select a.*from all_ind_columns a ;
select a.* from dba_ind_columns a ;
select a.* from user_ind_columns a 
  

 

  
1
  
2
  
3
 

5.4 列出表约束

查询 sys.all_constraints 和 sys.all_cons_columns

select a.TABLE_NAME, a.CONSTRAINT_NAME, b.COLUMN_NAME, a.CONSTRAINT_TYPE
  from all_constraints a, all_cons_columns b
 where a.TABLE_NAME = 'EMP'
   and a.OWNER = b.OWNER
   and a.TABLE_NAME = b.TABLE_NAME
   and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME;


TABLE_NAME       CONSTRAINT_NAME COLUMN_NAME                                                   CONSTRAINT_TYPE
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------------
EMP                       FK_DEPTNO          DEPTNO                                                                  R
EMP                       PK_EMP                 EMPNO                                                                   P

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
 

5.5 列出没有相应索引的外键

列出还有没有被索引的外键的表，例如 判断EMP表中的外键是否被索引。

select 
       a.TABLE_NAME, 
       a.CONSTRAINT_NAME, 
       a.COLUMN_NAME,
       c.INDEX_NAME
  from all_cons_columns a, all_constraints b, all_ind_columns c
 where a.TABLE_NAME = 'EMP'
   and a.OWNER = 'CRM'
   and b.CONSTRAINT_TYPE = 'R'
   and a.OWNER = b.OWNER
   and a.TABLE_NAME = b.TABLE_NAME
   and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
   and a.OWNER = c.TABLE_OWNER(+)
   and a.TABLE_NAME = c.TABLE_NAME(+)
   and a.COLUMN_NAME = c.COLUMN_NAME(+)
   and c.INDEX_NAME is null;


TABLE_NAME       CONSTRAINT_NAME COLUMN_NAME                                                   INDEX_NAME
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------
EMP                       FK_DEPTNO          DEPTNO                   
  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
 

5.6 使用SQL来生成SQL

举例：生成SQL来统计所有表中的行数

select 'select count(1) from ' || table_name || ';' from user_tables ; 
  

 

  
1
 

5.7 在oracle中描述数据字典视图

列出数据字典视图和他们的用途

   select * from dictionary  a order by a.TABLE_NAME ;
  

 

  
1
 

查询数据字典中的列

   select * from dict_columns a where a.TABLE_NAME = 'V$SQL';
  

 

  
1
 

文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。

原文链接：artisan.blog.csdn.net/article/details/68951517