oracle查询表、视图表结构以及视图创建语句

oracle的数据字典一般都有user、all、dba开始的三份，这里就以all开始的举例（注意权限，否则结果会很大）

1、all_tables：查询所有表相关信息

select OWNER,TABLE_NAME from all_tables;

2、all_tab_comments：查询所有表及视图注释

select OWNER,TABLE_NAME,TABLE_TYPE,COMMENTS from all_tab_comments;

 3、all_views：查询所有视图及视图定义语句

select OWNER,VIEW_NAME,TEXT from all_views;

4、all_tab_columns、all_tab_cols：这两个都表示查询所有表及视图结构，区别是后者包含隐藏列

select 
	OWNER "用户名",TABLE_NAME "表名",COLUMN_NAME "字段名",
	case 
		when DATA_PRECISION is null then DATA_TYPE || '(' || DATA_LENGTH || ')'
		else DATA_TYPE || '(' || DATA_PRECISION || ',' || DATA_SCALE || ')'
	end as "字段类型"
from all_tab_columns
--where OWNER='SCOTT'
order by OWNER,TABLE_NAME,COLUMN_ID

5、all_col_comments：查询所有表及视图的字段信息和字段注释

select OWNER,TABLE_NAME,COLUMN_NAME,COMMENTS from all_col_comments;