一、源表分析
- 查询统计业务系统的表名、表中文名、数据量,字段数,非空字段数,主键字段
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