create PROCEDURE calculateusedsize(in dbname varchar(128))
BEGIN
declare recordcount int(12);
declare tn varchar(128);
declare size int(12);
declare usedsize int(12);
DECLARE done INT DEFAULT 0;
DECLARE fetchtablecur CURSOR FOR SELECT qc.tablename , qc.size FROM system.columnsize qc;
declare continue handler for SQLSTATE '02000' set done = 1;
/*先删除再新建表,以后操作为insert*/
set @dropquotatableSQL= CONCAT('DROP TABLE IF EXISTS ',dbname,'.usedtable');
prepare s1 from @dropquotatableSQL;
execute s1 ;
deallocate prepare s1;
set @dropquotatableSQL=null;
set @createquotatableSQL= CONCAT('CREATE TABLE ',dbname,'.usedtable (Id int(11) NOT NULL auto_increment,
tablename varchar(128) NOT NULL ,
usedsize int(32) NOT NULL,
PRIMARY KEY (Id)
)');
prepare s1 from @createquotatableSQL;
execute s1 ;
deallocate prepare s1;
set @createquotatableSQL=null;
OPEN fetchtablecur;
REPEAT
FETCH fetchtablecur INTO tn, size;
IF NOT done THEN
#在动态sql中,将查询结果赋值给变量
set @v_sqlcounts = concat('select count(1) into @recordcount from ',dbname,'.',tn);
set @countsSelectSQL := @v_sqlcounts;
prepare s1 from @countsSelectSQL;
execute s1;
deallocate prepare s1;
set @v_sqlcounts =null;
set @countsSelectSQL =null;
set recordcount = @recordcount;
select recordcount*size into usedsize;
set @insertSQL= CONCAT('insert into ',dbname,'.usedtable(tablename,usedsize) values(\'',tn,'\',',usedsize,')');
prepare s1 from @insertSQL;
execute s1 ;
deallocate prepare s1;
set @insertSQL=null;
END IF;
UNTIL done END REPEAT;
CLOSE fetchtablecur;
END