Analyze all tables in a database?
 

How do I analyze all tables in a database?

Re: Analyze all tables in a database?
 

You can do it by a sp or from Mysql Administrator
I generally do it from Sp

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 ;