Oracle表结构分析

一、源表分析

• 查询统计业务系统的表名、表中文名、数据量，字段数，非空字段数，主键字段

SELECT 
	'数据表' AS 数据结构类型,
	A.TABLE_NAME AS 表名, 
	C.COMMENTS AS 中文名,
	B.NUM_ROWS AS 数据量,
	A.ZDS AS 字段数,
	CASE WHEN D.FKZDS IS NULL THEN 0
	ELSE D.FKZDS 
	END AS 非空字段数,
	E.COLUMN_NAME AS 主键
FROM 
(SELECT A.TABLE_NAME,COUNT(*) AS ZDS FROM USER_TAB_COLUMNS A  GROUP BY A.TABLE_NAME)A
LEFT JOIN USER_TABLES B ON A.TABLE_NAME=B.TABLE_NAME
LEFT JOIN USER_TAB_COMMENTS C ON C.TABLE_NAME=A.TABLE_NAME 
LEFT JOIN
(SELECT A.TABLE_NAME,COUNT(*) AS FKZDS FROM
(SELECT * FROM USER_TAB_COLUMNS WHERE NULLABLE='N')A GROUP BY A.TABLE_NAME)D ON D.TABLE_NAME=A.TABLE_NAME
LEFT JOIN 
(SELECT TO_CHAR(WM_CONCAT(CU.COLUMN_NAME)) AS COLUMN_NAME,CU.TABLE_NAME FROM USER_CONS_COLUMNS CU, USER_CONSTRAINTS AU
WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME AND AU.CONSTRAINT_TYPE = 'P'
GROUP BY CU.TABLE_NAME) E ON E.TABLE_NAME=A.TABLE_NAME
WHERE C.TABLE_TYPE<>'VIEW'--过滤视图
ORDER BY A.TABLE_NAME

---

---

二、字段分析

• 1、生成查询字段最大长度的语句

SELECT 'select '''||TABLE_NAME||''' AS TABLE_NAME,'''||COLUMN_NAME||''' AS column_name,'||'max(lengthB('||
COLUMN_NAME||')) as max_len from '||TABLE_NAME||' union' AS SELECT_MAXLENGTH
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME IN （此处加表筛选语句）

优化：含CLOB类型的字段最大长度变为NULL

SELECT 
	CASE WHEN DATA_TYPE='CLOB' THEN 
	 'select '''||TABLE_NAME||''' AS TABLE_NAME,'''||COLUMN_NAME||''' AS column_name,' ||' null as max_len from '||TABLE_NAME||' union' 
	 ELSE 
	'select '''||TABLE_NAME||''' AS TABLE_NAME,'''||COLUMN_NAME||''' AS column_name,'||' max(lengthB('||COLUMN_NAME||')) as max_len from '||TABLE_NAME||' union'  
	END AS SELECT_MAXLENGTH
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME IN（此处加表筛选语句）
ORDER BY TABLE_NAME,COLUMN_NAME

• 2、把字段最大长度结果存放在视图中USER_TAB_MAXLEN

CREATE VIEW USER_TAB_MAXLEN AS (/*查询字段最大长度的语句*/);
SELECT * FROM USER_TAB_MAXLEN;
--DROP VIEW  USER_TAB_MAXLEN;

• 3、生成函数

create or replace function display_raw (rawval raw, type varchar2)
  return varchar2
  is
     cn     number;
     cv     varchar2(4000);
     cd     date;
     cnv    nvarchar2(4000);
     cr     rowid;
     cc     char(32);
 begin
    if (type = 'NUMBER') then
       dbms_stats.convert_raw_value(rawval, cn);
       return to_char(cn);
    elsif (type = 'VARCHAR2') then
       dbms_stats.convert_raw_value(rawval, cv);
       return to_char(cv);
    elsif (type = 'DATE') then
       dbms_stats.convert_raw_value(rawval, cd);
       return to_char(cd);
    elsif (type = 'NVARCHAR2') then
       dbms_stats.convert_raw_value(rawval, cnv);
       return to_char(cnv);
    elsif (type = 'ROWID') then
       dbms_stats.convert_raw_value(rawval, cr);
       return to_char(cnv);
    elsif (type = 'CHAR') then
       dbms_stats.convert_raw_value(rawval, cc);
       return to_char(cc);
    else
       return 'UNKNOWN DATATYPE';
    end if;
 end;

• 4、查询表的表名、字段名、字段注释、数据类型、数据长度，最大值、解释举例

SELECT 
	A.TABLE_NAME      AS 表名,
	A.COLUMN_NAME  AS 字段名,
	B.COMMENTS    AS 字段注释,
	CASE 
	WHEN A.DATA_TYPE = 'VARCHAR2' THEN 'C'
	WHEN A.DATA_TYPE ='NUMBER' THEN 'N'
	WHEN A.DATA_TYPE ='CLOB' THEN 'CL'
	WHEN A.DATA_TYPE ='DATE' THEN 'D'
	WHEN A.DATA_TYPE ='BLOB' THEN 'BL'
	ELSE A.DATA_TYPE END AS 数据类型,
	A.DATA_LENGTH     AS 数据长度,
	C.MAX_LEN AS 最大长度,
	CASE WHEN C.MAX_LEN IS NULL THEN '(空字段)'
	ELSE '最小值:'||DISPLAY_RAW(A.LOW_VALUE,DATA_TYPE)||', 最大值:'||DISPLAY_RAW(A.HIGH_VALUE,DATA_TYPE) END AS 解释举例
FROM USER_TAB_COLUMNS A
LEFT JOIN USER_COL_COMMENTS B ON (A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME)
LEFT JOIN USER_TAB_MAXLEN C ON (A.TABLE_NAME=C.TABLE_NAME AND A.COLUMN_NAME=C.COLUMN_NAME)
WHERE A.TABLE_NAME IN （此处加表筛选语句）
ORDER BY A.TABLE_NAME