Jump to content

Recommended Posts

Posted

Team,

 

Recently we have migrated to AWS and using DB as Mariadb.  Below is the code used to perform "Analyze table"...

 

DELIMITER $$
CREATE DEFINER=`xyz`@`%` PROCEDURE `ANALYZE_OF_TABLES`()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE TNAME CHAR(255);
  DECLARE DATALOAD CHAR(255);
  DECLARE table_names CURSOR for 
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN table_names;   
WHILE done = 0 DO
  FETCH NEXT FROM table_names INTO TNAME;
   IF done = 0 THEN
    SET @SQL_TXT = CONCAT(' ANALYZE TABLE ', TNAME, ' PERSISTENT FOR COLUMNS () INDEXES () ');
      PREPARE stmt_name FROM @SQL_TXT;
    EXECUTE stmt_name;
    DEALLOCATE PREPARE stmt_name;  
END IF;
END WHILE;
CLOSE table_names;
SET DONE=1;
END$$
DELIMITER ;
 

Code Execution : Perfectly , running 

 

Issue : I would like to see the Analyze on the table done as below :

  Analyze successfully completed for table  ABC

  Analyze successfully completed for table  XYZ...

currently i see the code displayed for each table as IN each tab 

 

DATABASE.TABLE_NAME   analyze  status Engine-independent statistics collected
DATABASE.TABLE_NAME
Analyze status OK

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...