DELIMITER $$
 
DROP PROCEDURE IF EXISTS `reports`.`SP_Mysql_Maintance_Plan_Optimize_Tables`$$
 
CREATE PROCEDURE `SP_Mysql_Maintance_Plan_Optimize_Tables`()
BEGIN
DECLARE l_loop_end INT DEFAULT 0;
DECLARE  IN_TABLE_SCHEMA VARCHAR(100);
DECLARE  IN_TABLE_NAME VARCHAR(100);
DECLARE CUR1 CURSOR FOR
SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA  NOT IN ('TEST','information_schema');
DECLARE continue HANDLER for sqlstate '02000' SET l_loop_end = 1;
open CUR1;
REPEAT
FETCH cur1 INTO IN_TABLE_SCHEMA,IN_TABLE_NAME;
if NOT l_loop_end THEN
SELECT '' AS '';
SELECT CONCAT('DATABASE NAME: ' ,IN_TABLE_SCHEMA,'   TABLE NAME : ',IN_TABLE_NAME)  AS '';
SELECT CONCAT('START TIME: ',NOW()) as '';
SET @qry = CONCAT('OPTIMIZE TABLE ', IN_TABLE_SCHEMA, '.',IN_TABLE_NAME);
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT CONCAT('END TIME: ',NOW()) AS '';
SELECT '' AS '';
END if;
until l_loop_end END REPEAT;
close cur1;
END$$
 
DELIMITER ;