【oracle】oracle中繼資料擷取使用者下的表關鍵資訊

目錄

• 擷取該使用者下所有表名和表描述
• 擷取檢視該使用者下是以表字段資訊(除字段描述)
• 擷取字段描述
• 擷取表的主鍵資訊
• 擷取使用者下表關鍵資訊

了解一個業務系統的資料情況，所有要了解該業務系統的表結構資訊及ER關系圖。本文主要聊聊通過oracle中繼資料擷取該使用者下所有表的關鍵資訊，比如：表名、表描述、字段名、字段描述、字段類型、字段長度、是否主鍵、是否非空、預設值等。

擷取該使用者下所有表名和表描述

user_tab_comments表(視圖)，該表存儲的是該使用者下所有表和描述

總共三個字段：

字段名	解釋
table_name	表名
table_type	表類型(table、view)
comments	表描述

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_name	表名
column_name	字段名
data_type	字段類型(number、varchar2等)
data_length	字段長度
data_precision	字段精度(比如number類型的整數位)
data_scale	字段小數範圍
nullable	是否為空(N:不為空,Y:可為空)
column_id	字段順序序号
default_length	預設長度
data_default	預設值
num_distinct	字段去重數(count(distinct col))
low_value	字段最小值
high_value	字段最大值
density	密度
num_nulls	空值數
num_buckets	桶數
last_analyzed	最後分析時間(未知具體含義，望大佬解釋)
sample_size	樣品大小(未知具體含義)
character_set_nane	未知具體含義
char_col_decl_length	未知具體含義
global_stats	整體狀态(未知具體含義)
user_stats	使用者狀态(未知具體含義)
avg_col_len	字段平均長度(可以計算實際存儲大小)
char_length	字元長度
char_used	字元機關(B)
v80_fmt_image	未知具體含義
data_upgraded	未知具體含義
histogram	未知具體含義

擷取字段描述

user_col_comments表(視圖)擷取該使用者下所有表字段描述

注: 其中有table_name類似于下圖的，這些是被删除存在資源回收筒的表

該表總共三個字段:

字段名	描述
table_name	表名
column_name	字段名
comments	字段描述

擷取表的主鍵資訊

擷取主鍵字段資訊

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視圖，存儲表限制相關資訊。

關鍵字段：

字段名	描述
owner	所有者
constraint_name	限制名稱
constraint_type	限制類型(P:主鍵,U:唯一,F:外鍵等)
table_name	表名
r_owner	上一個所有者
r_constraint_name	上一個限制名稱
index_owner	索引所有者
index_name	索引名稱

user_cons_columns視圖，存儲限制的字段資訊。

關鍵字段：

字段名	描述
owner	所有者
constraint_name	限制名稱
table_name	表名
column_name	字段名稱
position	位置

擷取使用者下表關鍵資訊

通過以上表整合擷取使用者下關鍵資訊

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 --保證字段順序和原表字段順序一緻
;