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

落日映苍穹つ 2021-09-25 01:24 677阅读 0赞

### 目录 ###

*  获取该用户下所有表名和表描述
 *  获取查看该用户下所以表字段信息(除字段描述)
 *  获取字段描述
 *  获取表的主键信息
 *  获取用户下表关键信息

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

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

user\_tab\_comments表(视图)，该表存储的是该用户下所有表和描述

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

总共三个字段：

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>table_name</td> 
   <td>表名</td> 
  </tr> 
  <tr> 
   <td>table_type</td> 
   <td>表类型(table、view)</td> 
  </tr> 
  <tr> 
   <td>comments</td> 
   <td>表描述</td> 
  </tr> 
 </tbody> 
</table>

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
    ;

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

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>table_name</td> 
   <td>表名</td> 
  </tr> 
  <tr> 
   <td>column_name</td> 
   <td>字段名</td> 
  </tr> 
  <tr> 
   <td>data_type</td> 
   <td>字段类型(number、varchar2等)</td> 
  </tr> 
  <tr> 
   <td>data_length</td> 
   <td>字段长度</td> 
  </tr> 
  <tr> 
   <td>data_precision</td> 
   <td>字段精度(比如number类型的整数位)</td> 
  </tr> 
  <tr> 
   <td>data_scale</td> 
   <td>字段小数范围</td> 
  </tr> 
  <tr> 
   <td>nullable</td> 
   <td>是否为空(N:不为空,Y:可为空)</td> 
  </tr> 
  <tr> 
   <td>column_id</td> 
   <td>字段顺序序号</td> 
  </tr> 
  <tr> 
   <td>default_length</td> 
   <td>默认长度</td> 
  </tr> 
  <tr> 
   <td>data_default</td> 
   <td>默认值</td> 
  </tr> 
  <tr> 
   <td>num_distinct</td> 
   <td>字段去重数(count(distinct col))</td> 
  </tr> 
  <tr> 
   <td>low_value</td> 
   <td>字段最小值</td> 
  </tr> 
  <tr> 
   <td>high_value</td> 
   <td>字段最大值</td> 
  </tr> 
  <tr> 
   <td>density</td> 
   <td>密度</td> 
  </tr> 
  <tr> 
   <td>num_nulls</td> 
   <td>空值数</td> 
  </tr> 
  <tr> 
   <td>num_buckets</td> 
   <td>桶数</td> 
  </tr> 
  <tr> 
   <td>last_analyzed</td> 
   <td>最后分析时间(未知具体含义，望大佬解释)</td> 
  </tr> 
  <tr> 
   <td>sample_size</td> 
   <td>样品大小(未知具体含义)</td> 
  </tr> 
  <tr> 
   <td>character_set_nane</td> 
   <td>未知具体含义</td> 
  </tr> 
  <tr> 
   <td>char_col_decl_length</td> 
   <td>未知具体含义</td> 
  </tr> 
  <tr> 
   <td>global_stats</td> 
   <td>整体状态(未知具体含义)</td> 
  </tr> 
  <tr> 
   <td>user_stats</td> 
   <td>用户状态(未知具体含义)</td> 
  </tr> 
  <tr> 
   <td>avg_col_len</td> 
   <td>字段平均长度(可以计算实际存储大小)</td> 
  </tr> 
  <tr> 
   <td>char_length</td> 
   <td>字符长度</td> 
  </tr> 
  <tr> 
   <td>char_used</td> 
   <td>字符单位(B)</td> 
  </tr> 
  <tr> 
   <td>v80_fmt_image</td> 
   <td>未知具体含义</td> 
  </tr> 
  <tr> 
   <td>data_upgraded</td> 
   <td>未知具体含义</td> 
  </tr> 
  <tr> 
   <td>histogram</td> 
   <td>未知具体含义</td> 
  </tr> 
 </tbody> 
</table>

# 获取字段描述 #

user\_col\_comments表(视图)获取该用户下所有表字段描述

select table_name, columns_nam, comments from user_col_comments;

注: 其中有table\_name类似于下图的，这些是被删除存在回收站的表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70]  
该表总共三个字段:

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>table_name</td> 
   <td>表名</td> 
  </tr> 
  <tr> 
   <td>column_name</td> 
   <td>字段名</td> 
  </tr> 
  <tr> 
   <td>comments</td> 
   <td>字段描述</td> 
  </tr> 
 </tbody> 
</table>

# 获取表的主键信息 #

获取主键字段信息

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'
    ;

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

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>owner</td> 
   <td>所有者</td> 
  </tr> 
  <tr> 
   <td>constraint_name</td> 
   <td>约束名称</td> 
  </tr> 
  <tr> 
   <td>constraint_type</td> 
   <td>约束类型(P:主键,U:唯一,F:外键等)</td> 
  </tr> 
  <tr> 
   <td>table_name</td> 
   <td>表名</td> 
  </tr> 
  <tr> 
   <td>r_owner</td> 
   <td>上一个所有者</td> 
  </tr> 
  <tr> 
   <td>r_constraint_name</td> 
   <td>上一个约束名称</td> 
  </tr> 
  <tr> 
   <td>index_owner</td> 
   <td>索引所有者</td> 
  </tr> 
  <tr> 
   <td>index_name</td> 
   <td>索引名称</td> 
  </tr> 
 </tbody> 
</table>

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 --保证字段顺序和原表字段顺序一致
    ;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70]: /images/20210923/899f3b8d8ab24926b266f3f30ed4d9e2.png