1.列出所有表
select table_name from all_tables
2.列出表EMP的所有列
select * from all_tab_columns where owner='DMS' and table_name='EMP'
3.列出表EMP的索引
select table_name,index_name,column_name,column_position
from all_ind_columns where table_name = 'EMP' and table_owner = 'DMS'
4.列出表约束
空缺
5.列出没有相应索引的外键
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 = 'DMS'
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
6.使用SQL来生成SQL
①查询所有表的行数
select 'select count(*) from ' || table_name || ';' cnts from user_tables;
②禁用所有表的外键
select 'alter table '|| table_name || 'disable constraint ' || constraint_name||';' cons
from user_constraints where constraint_type = 'R'
③根据表EMP中的某些列生成插入脚本
select 'insert into emp(empno,ename,hiredate) '|| chr(10)||'values('||empno||','||''''||ename
|| ''',to_date('||''''||hiredate||'''));'inserts from emp where deptno = 10;
7.查看数据字典里的表和对于表的描述及列的描述
select table_name,comments from dictionary
select * from dict_columns