oracle整库统计库表各个字段数据最大长度--存储过程

 1 CREATE OR REPLACE PROCEDURE TABLES_COL_MAX AS
 2   COUNT_SQL    VARCHAR2(2000);
 3   P_TABLE_NAME VARCHAR2(2000);
 4   P_COL_NAME   VARCHAR2(2000);
 5   P_COL_TYPE   VARCHAR2(2000);
 6   P_DATA_LEN   VARCHAR2(2000);
 7   EXCEPT_SQL VARCHAR2(2000);
 8 BEGIN
 9   EXECUTE IMMEDIATE 'DROP TABLE T_ROWS_MAX';
10   EXECUTE IMMEDIATE 'CREATE TABLE T_ROWS_MAX (TABLE_NAME varchar2(200)  ,COL_NAME varchar2(200),COL_TYPE varchar2(50), LENMAX number)';
11   FOR CUR IN (select uc.TABLE_NAME, uc.COLUMN_NAME, uc.data_type,uc.data_length
12                 from user_tab_columns uc) LOOP
13     P_TABLE_NAME := CUR.TABLE_NAME;
14     P_COL_NAME   := CUR.COLUMN_NAME;
15     P_COL_TYPE   := CUR.data_type;
16     P_DATA_LEN   := CUR.data_length;
17     IF P_COL_TYPE <>'BLOB' OR P_COL_TYPE <>'CLOB' THEN
18       BEGIN
19         COUNT_SQL    := 'insert into  T_ROWS_MAX(TABLE_NAME,COL_NAME,COL_TYPE,LENMAX)  SELECT ''' ||
20                     P_TABLE_NAME || '''AS  TABLE_NAME , ''' || P_COL_NAME ||
21                     '''AS  COL_NAME, ''' || P_COL_TYPE ||
22                     '''AS  COL_TYPE, MAX(LENGTH('||P_COL_NAME||'
23                     )) AS LENMAX  FROM ' || P_TABLE_NAME;
24         EXECUTE IMMEDIATE COUNT_SQL;
25         COMMIT;
26       EXCEPTION
27         WHEN OTHERS THEN
28         EXCEPT_SQL    := 'insert into  T_ROWS_MAX(TABLE_NAME,COL_NAME,COL_TYPE,LENMAX)  SELECT ''' ||
29                     P_TABLE_NAME || '''AS  TABLE_NAME , ''' || P_COL_NAME ||
30                     '''AS  COL_NAME, ''' || P_COL_TYPE ||
31                     '''AS  COL_TYPE, '|| P_DATA_LEN ||'  AS LENMAX  FROM ' || P_TABLE_NAME;
32 
33         EXECUTE IMMEDIATE EXCEPT_SQL;
34         COMMIT;
35       END;
36      END IF;
37   END LOOP;
38 
39 END TABLES_COL_MAX;