Analyze all tables in a database?

[desiguru]

How do I analyze all tables in a database?

[varmadba]

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 ;