oracle库内置对象各项查询功能（持续更新……）

前言

写本篇文章的原因是因为在工作中遇到很多查询需求，这些需求需要从oracle库内置的对象（表、视图等）进行查询，功能也很实用。

比如：要统计指定用户下所有的表英文名、表中文名、字段英文名、字段中文名、字段类型等。数据如果很大的话，靠复制粘贴是不行的，效率太慢还容易出错。

所以此篇文章总结了一些日常用到的操作，并且会持续更新文章。

0. 总结清单

为了方便大家查询，先把用到的各个来源对象做个总结，具体使用情况可继续翻看下面的内容。

来源-当前用户	来源-所有用户	描述	区别
user_users	all_users	查看用户信息	all_users 可指定用户名
user_tables	all_tables	查看表英文名、表空间等信息	all_tables 可指定用户名
user_tab_comments	all_tab_comments	查看表英文名、表中文名	all_tab_comments可指定用户名
user_col_comments	all_col_comments	查看表英文名、字段英文名、字段中文名	all_col_comments可指定用户名
user_tab_columns	all_tab_columns	查看字段类型、长度	all_tab_columns可指定用户名
user_objects	all_objects	查看各类对象（表、存储过程、函数、序列、触发器、索引等）	all_objects可指定用户名
user_source	all_source	查看某个表或想查询的其他内容在哪个对象里存在	all_source可以指定用户名

1. 查看用户相关的信息

--查看当前用户信息
select * from user_users; --可查看用户名称、用户ID、所属表空间等信息

--查看所有用户信息
select * from all_users a
where a.username = '用户名';  --可以筛选具体的用户名

举例

all_users 可以查看所有用户的用户名称、ID、创建时间，如下图所示。

2. 查看表相关的信息

2.1 查看表英文名、表空间

--查看指定用户下所有的表信息（表英文名、表空间等信息）
select a.table_name,a.tablespace_name 
from all_tables a
where a.owner = '用户名' --筛选用户名
order by a.table_name ;

举例，查看scott 用户下的表。

2.2 查看表英文名+表中文名

--查看指定用户下表英文名和中文名
select a.OWNER,a.TABLE_NAME,a.COMMENTS
from all_tab_comments a
where a.owner = '用户名'
and a.table_type = 'TABLE'  --因为筛选结果会包含TABLE、VIEW，这里只筛选出TABLE
order by a.table_name;

举例，查看 msl_user1用户下所有的表英文名和中文名

3. 查看字段相关的信息

3.1 查看字段英文名、字段中文名

--查看指定用户、指定表的字段信息（字段英文名、字段中文名等信息）
select a.owner,a.table_name,a.column_name,a.comments 
from all_col_comments a
where a.owner = '用户名'     --指定用户名
and a.table_name = '表名'    --指定表名

举例

3.2 查看各字段数据类型、数据长度

select a.date_type,a.data_length 
from all_tab_columns a
where a.owner = '用户名'     --指定用户名
--and a.table_name = '表名'  --可筛选某个表
and a.table_name = '表名'    --指定表名

举例

4. 查看各类对象（表、存储过程、试图、序列、函数、触发器）

select * from all_objects a
where a.owner = '用户名'    --指定用户名
and 
a.object_type = 'TABLE'          --查找表
--a.object_type = 'VIEW'         --查找试图
--a.object_type = 'SEQUENCE'     --查找序列
--a.object_type = 'PROCEDURE'    --查找存储过程
--a.object_type = 'FUNCTION'     --查找函数
--a.object_type = 'TRIGGER'      --查找触发器
--a.object_type = 'INDEX'        --查找索引
order by a.object_name    --按对象名称排序

举例

5. 查看某个表在哪个对象里用到

--查看某个表在哪个存储过程或函数里用到了
select * from all_source 
where owner = '用户名'
and upper(text) = '表名大写' --不清楚text里面是大写或小写，所以进行统一转换

举例
比如想查当前用户下，哪些对象用到了 all_objects 这张表，如下图所示，在多个PACKAGE、PACKAGE BODY、PROCUDURE里都用到了。

user_source 或 all_suorce 的关键在于 TEXT 这一列，筛选的时候可以根据需求进行模糊查询，下面的例子只模糊查询了表名，也可以在表名前加上 insert into 、merge into 等你确定的信息

6. 工作中实用操作举例

6.1 同时查看表英文名、表中文名、字段英文名、字段中文名、字段类型

统计出当前用户下所有的表英文名、表中文名、字段英文名、字段中文名、字段类型，形成一个结果集。

select
a.table_name,	--表英文名
a.comments,		--表中文名
b.column_name,	--字段英文名
b.comments,		--字段中文名
c.data_type,	--数据类型
c.data_length	--数据长度
from user_tab_comments a
left join user_col_comments b
on a.table_name = b.table_name
left join user_tab_columns c
on a.table_name = c.table_name
and b.column_name = c.column_name
order by a.table_name;

6.2 统计当前用户下所有的存储过程

select * from user_objects a
where a.object_type = 'PROCEDURE';