【oracle】oracle元数据获取用户下的表关键信息_oracle dataprecision

了解一个业务系统的数据情况，所有要了解该业务系统的表结构信息及ER关系图。本文主要聊聊通过oracle元数据获取该用户下所有表的关键信息，比如：表名、表描述、字段名、字段描述、字段类型、字段长度、是否主键、是否非空、默认值等。

获取该用户下所有表名和表描述

user_tab_comments表(视图)，该表存储的是该用户下所有表和描述

select table_name, comments from user_tab_comments where table_type = 'TABLE';
1

总共三个字段：

user_tables表(视图)，该表存储该用户下所有表相关信息，包括表的行数(num_rows)、块数(blocks)、字段平均大小(avg_row_len)等信息。

all_tables表(视图)，该表存储所有用户下所有表相关信息，表结构和user_table表一样。

dba_tables表(视图)，该表存储系统内所有表相关信息，包括系统表，结构和user_table表一样。

获取查看该用户下所以表字段信息(除字段描述)

user_tab_columns表(视图)，在sys用户下的视图，来源于user_tab_cols表，存储该用户下所有表的字段信息，不包括字段描述和约束等。

select table_name  --表名
      ,column_name  --字段名
      ,data_type  --字段类型
      ,data_length  --字段长度
      ,data_precision  --字段精度(理解为整数位数)
      ,data_scale  --字段小数位位数
      ,nullable  --是否可为null
      ,data_default  --默认值
  from user_tab_columns
;
1
2
3
4
5
6
7
8
9
10

该表主要关键字段有如下：

获取字段描述

user_col_comments表(视图)获取该用户下所有表字段描述

select table_name, columns_nam, comments from user_col_comments;
1

注: 其中有table_name类似于下图的，这些是被删除存在回收站的表

该表总共三个字段:

获取表的主键信息

获取主键字段信息

select ucc.table_name, ucc.column_name
  from user_cons_columns ucc, user_constraints uc
 where uc.constraint_name = ucc.constraint_name
   and uc.constraint_type = 'P'
;
1
2
3
4
5

user_constraints视图，存储表约束相关信息。
关键字段：

user_cons_columns视图，存储约束的字段信息。
关键字段：

获取用户下表关键信息

通过以上表整合获取用户下关键信息

select t1.table_name --表名称
      ,t1.comments as table_comment  --表描述
      ,t2.column_name  --字段名称
      ,t3.comments as column_comment  --字段描述
      --,t2.data_type  --字段类型
      --,t2.data_length  --字段长度
      --,t2.data_precision  --字段精度
      --,t2.data_scale  --字段小数范围
      ,t2.data_type_new  --组合的字段类型
      ,case when t4.table_name is not null then 'Y'
       else null end is_pk  --是否为主键
      ,t2.is_not_null  --是否为空
      ,t2.data_default  --默认值
  from 
  ( --该用户下表名和表描述
  	select table_name, comments
  	  from user_tab_comments 
  	 where table_type = 'TABLE'
  ) t1
  left join
  ( --该用户下表名、字段信息
  	select table_name  --表名
          ,column_name  --字段名
          ,data_type  --字段类型
          ,data_length  --字段长度
          ,data_precision  --字段精度(理解为整数位数)
          ,data_scale  --字段小数位位数
          ,case when nullable = 'N' then 'Y'
           else null end as is_not_null  --是否非空
          ,data_default  --默认值
          ,case when data_precision is not null and data_scale is not null then data_type||'('||data_precision||','||data_scale||')'
                when data_precision is not null and data_scale is null then data_type||'('||data_precision||')'
                when data_precision is null and data_scale is null and data_length is not null then data_type||'('||data_length||')'
           else data_type end as data_type_new
				,column_id	 
      from user_tab_columns
  ) t2 on t1.table_name = t2.table_name
  left join
  (
	--该用户下表名和字段描述
	select table_name
	      ,column_name
	      ,comments
	  from user_col_comments
  ) t3 on t2.table_name = t3.table_name and t2.column_name = t3.column_name
  left join
  ( --该用户下的主键信息
  	select ucc.table_name, ucc.column_name
  	  from user_cons_columns ucc, user_constraints uc
  	 where uc.constraint_name = ucc.constraint_name
       and uc.constraint_type = 'P'
  ) t4 on t2.table_name = t4.table_name and t2.column_name = t4.column_name
 order by t1.table_name, t2.column_id --保证字段顺序和原表字段顺序一致
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54