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;