gbase8a计算所有表行数和大小

-- 设置会话参数
set session _gbase_query_path=on;

-- 进入数据库
use test;

– 新建表
create table test.tb_size(table_schema varcher(200),table_name varchar(400),table_rows bigint(20),storage_size varchar(100));

-- 创建存储过程
DELIMITER //
create  procedure tb_collect ()
begin 
    declare v_table_schema varchar(200);
    declare v_table_name varchar(400);
    declare v_table_rows bigint(20);
    declare v_storage_size varchar(100);
    declare done int default false;
    declare cur cursor for select table_schema,table_name from information_schema.tables where table_schema not in ('information_schema','performance_schema','gbase','gctmpdb','gclusterdb') and  TABLE_TYPE  <> 'VIEW' ;
    declare continue HANDLER for not found set done = true;
    declare continue HANDLER for SQLSTATE 'HY000'  begin end;
        open cur ;
read_loop:loop
            fetch cur into v_table_schema,v_table_name;
            if done then
                leave read_loop;
            end if;
            set @analy_sql1 = concat('insert into test.tb_size ', 'select table_schema,table_name,table_rows,storage_size from performance_schema.tables where   table_schema=''',v_table_schema,''' and table_name= ''',v_table_name,''';');
            PREPARE analy1 FROM @analy_sql1; 
            -- 如果有变量 EXECUTE analy1 using @var; 
            EXECUTE analy1 ; 
            deallocate prepare analy1; 
            -- select @analy_sql1;
end loop;
        close cur;
end; //

-- 修改语句结束为;
DELIMITER ;

-- 调用存储过程
call tb_collect ();

-- 查询数据
select table_schema,table_name,table_rows,storage_size/1024/1024 "storage_size(MB)" from test.tb_size;

select table_schema,table_name,table_rows,storage_size/1024/1024 "storage_size(MB)" from test.tb_size order by storage_size/1024/1024 desc;

-- 删除存储过程

DROP PROCEDURE IF EXISTS tb_collect;

-- 查看存储过程
SHOW CREATE PROCEDURE vc1.demo.proc_1\G
select * from gbase.proc\G