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