【达梦系列】统计模式下所有的表名以及表的数据量

一、创建存储表方式

--创建表存储信息
create table test(table_name varchar,cnt bigint);
--将以下SYSDBA替换成需要统计的模式名
declare
sql varchar ;
cnt bigint ;
begin
for rec in (select table_name from dba_tables where owner = 'SYSDBA') loop
 sql = 'select count(*) from SYSDBA."'||rec.table_name||'"';
 execute immediate sql into cnt;
 insert into test values (rec.table_name,cnt);
 commit;
end loop;
select *from  test;
--最后记得删除这个表
end;

二、使用系统表ALL_TABLES

SELECT T.TABLE_NAME AS 表名,TABLE_ROWCOUNT(T.OWNER,T.TABLE_NAME) AS 数据量,U.COMMENTS AS 注释
FROM ALL_TABLES T,ALL_TAB_COMMENTS U where T.TABLE_NAME =U.TABLE_NAME AND T.OWNER='WHAPTS'
--或者
SELECT OWNER,TABLE_NAME,NUM_ROWS,* FROM SYS.ALL_TABLES 
WHERE OWNER IN (
SELECT NAME FROM SYS.SYSOBJECTS 
  WHERE TYPE$='SCH'
 AND PID IN(SELECT ID FROM SYS.SYSOBJECTS WHERE TYPE$='UR' AND SUBTYPE$='USER' AND NAME  IN('WHAPTS'))
) order by owner,table_name