虚谷数据库查询大表比例，空表比例，索引比例

一、背景

在使用虚谷数据库时，想了解虚谷数据库存储情况，比如大表占多少，空表占多少，索引占多少，如果能用一个存储过程展示出来就相当方便了

二、虚谷存储过程

--展示库的存储情况,比如大表比例，比如空表比例，索引比例
CREATE OR REPLACE procedure analyze_db as 
declare 
large_table_num int;
table_all_nums  int;
small_table_nums int;
hava_idx_tb_num int;
v_over_mb int ;
begin 
 v_over_mb:=1000; -- 可以自定义多大的表为大表，只需要改这里，其他不用改
--大于v_over_mb MB 为大表
for i in (select db_id,db_name from sys_databases) loop
   select count(*) into large_table_num from (
	select table_name,table_size from (
	 select d.db_name,s.schema_name,t.table_name,8*count(*) as table_size from sys_schemas s,sys_tables t,sys_gstores g,sys_databases d 
	    where g.obj_id=t.table_id and s.schema_id=t.schema_id and s.db_id=t.db_id and g.db_id=d.db_id  and t.db_id=d.db_id  and d.db_id=i.db_id
	    group by t.table_name,s.schema_name,d.db_name order by d.db_name,s.schema_name desc
	    )
	where table_size>v_over_mb);
	
	select count(*) into table_all_nums from sys_tables  where db_id=i.db_id;
-- 空表比例
	select count(*) into small_table_nums from (
	 select d.db_name,s.schema_name,t.table_name,8*count(*) as table_size from sys_schemas s,sys_tables t,sys_gstores g,sys_databases d 
    where g.obj_id=t.table_id and s.schema_id=t.schema_id and s.db_id=t.db_id and g.db_id=d.db_id  and t.db_id=d.db_id  and d.db_id=i.db_id
    group by t.table_name,s.schema_name,d.db_name order by d.db_name,s.schema_name desc
    )
    where table_size=8 ;
   
   select count(*) into hava_idx_tb_num  from (
	select table_id,count(*) idx_num from sys_indexes where db_id=i.db_id  group by table_id
	);
    
	if table_all_nums!=0 then
		send_msg('库: '||i.db_name||' 大表(>'|| v_over_mb||'MB)比例: '||substr(large_table_num/table_all_nums,0,4)||'% ,'||'空表比例: '|| substr(small_table_nums/table_all_nums,0,4)||'% ,'|| '索引比例:'|| substr(hava_idx_tb_num/table_all_nums,0,4)||'%');
    end if;
  end loop;

 end analyze_db

三、调用存储过程

call analyze_db()

四、结果展示