达梦DM-统计用户下每个表的行数和数据量大小

1，统计用户下每个表的行数和数据量大小
–创建一张临时表，用来记录每张表的数据量情况

create table table_count (owner varchar(100),table_name varchar(100),cnt int);
–执行存储过程统计指定模式每张表数据条数
模式名改为要查询的对应的模式即可

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

declare v_owner VARCHAR2(100); v_tabname VARCHAR2(100); stmt  VARCHAR2(200); num_rows number; begin     for rec in (select owner,table_name from dba_tables where owner='模式名' order by 1, 2)     loop         select rec.owner,rec.table_name into v_owner,v_tabname from dual;         stmt := 'select count(*) from "' || v_owner || '"."' || v_tabname || '"';         EXECUTE IMMEDIATE stmt INTO num_rows;         EXECUTE IMMEDIATE 'insert into table_count values('''||v_owner||''','''||v_tabname||''','''||to_number(num_rows)||''');';     end loop; end;

–查询各表数据条数

select * from table_count;
–统计所有表数据总量

select sum(cnt) from table_count