关于Oracle数据库统计表信息的存储过程

 统计oracle中表名，表注释，字段名，字段注释，字段类型，行数相关存储过程

CREATE TABLE COUNT_TAB 
   ("OWNERS" VARCHAR2(255), 
    "TABLES" VARCHAR2(255), 
    "TABLES_COMMENTS" VARCHAR2(4000), 
    "COLUMNS" VARCHAR2(255), 
    "COLUMNS_COMMENTS" VARCHAR2(4000), 
    "DAYA_TYPE" VARCHAR2(255), 
    "COUNTS" VARCHAR2(255),
  "TIME" VARCHAR2(255)
   )
  
----单表
  CREATE OR REPLACE PROCEDURE all_count_tab_col_single as
  ls_owner VARCHAR2(200); -- owner
  ls_table VARCHAR2(200); -- 表名
  ls_tab_com VARCHAR2(4000); -- 表注释
  ls_column VARCHAR2(200);-- 字段名
  ls_col_com VARCHAR2(4000); -- 字段注释
  ls_data_type VARCHAR2(200); -- 字段类型
  sqlflag VARCHAR2(200);
  ls_count LONG;--行数
begin
  for i in (SELECT * FROM all_tab_comments where table_type = 'TABLE' and owner = 'HYH' and table_name = 'DEPT') loop
  for j in (SELECT a.owner,a.table_name,a.column_name,data_type||'('||data_length||')' datatype,b.comments
  from all_tab_columns a,all_col_comments b where a.owner = b.owner and a.table_name = b.table_name and a.column_name = b.column_name and upper(a.table_name) = upper(I.table_name) and upper(a.owner) = upper(I.owner)) loop
                ls_owner := J.OWNER;
                ls_table := J.TABLE_NAME;
                ls_tab_com := i.comments;
                ls_column:= J.COLUMN_NAME;
                ls_col_com := j.comments;
                ls_data_type := j.datatype; 
          sqlflag := 'select count(*) from '||ls_owner||'."'||ls_table||'" where "'||ls_table||'"."'||ls_column||'" is not null';
          execute immediate sqlflag into ls_count;
          INSERT INTO count_tab VALUES(LS_OWNER,ls_table,ls_tab_com,ls_column,ls_col_com,ls_data_type,ls_count,TO_CHAR(SYSDATE(),'YY/MM/DD HH24:MI:SS') );
                END LOOP;
        commit;
  END LOOP;
END;


--排除
CREATE OR REPLACE PROCEDURE all_count_tab_col as
  ls_owner VARCHAR2(200); -- owner
  ls_table VARCHAR2(200); -- 表名
  ls_tab_com VARCHAR2(4000); -- 表注释
  ls_column VARCHAR2(200);-- 字段名
  ls_col_com VARCHAR2(4000); -- 字段注释
  ls_data_type VARCHAR2(200); -- 字段类型
  sqlflag VARCHAR2(200);
  ls_count LONG;--行数
begin
  for w in (select * from all_users where USER_ID > 107 and USER_ID < 1279990) loop
  for i in (SELECT * FROM all_tab_comments where table_type = 'TABLE' and owner = upper(w.username) and table_name not in upper('dept') and owner not in upper('hyh')) loop
  for j in (SELECT a.owner,a.table_name,a.column_name,data_type||'('||data_length||')' datatype,b.comments
  from all_tab_columns a,all_col_comments b where a.owner = b.owner and a.table_name = b.table_name and a.column_name = b.column_name and upper(a.table_name) = upper(I.table_name) and upper(a.owner) = upper(I.owner)) loop
                ls_owner := J.OWNER;
                ls_table := J.TABLE_NAME;
                ls_tab_com := i.comments;
                ls_column:= J.COLUMN_NAME;
                ls_col_com := j.comments;
                ls_data_type := j.datatype; 
          sqlflag := 'select count(*) from '||ls_owner||'."'||ls_table||'" where "'||ls_table||'"."'||ls_column||'" is not null';
          execute immediate sqlflag into ls_count;
          INSERT INTO count_tab VALUES(LS_OWNER,ls_table,ls_tab_com,ls_column,ls_col_com,ls_data_type,ls_count,TO_CHAR(SYSDATE(),'YY/MM/DD HH24:MI:SS') );
                END LOOP;
        commit;
        END LOOP;
  END LOOP;
END;