gistfile1.txt

-- Source: https://mysqlstepbystep.com/2017/04/24/handy-stored-procedure-for-regular-dba-tasks/

DROP PROCEDURE IF EXISTS `reorganize_tables`;

DELIMITER //

CREATE PROCEDURE `reorganize_tables` (`db_name` VARCHAR(64))
BEGIN
  DECLARE `v_finished` BOOL DEFAULT FALSE;
  DECLARE `tbl_name` VARCHAR(64) DEFAULT '';
  DECLARE `all_tbl_cursor` CURSOR FOR
  SELECT `ist`.`TABLE_NAME`
  FROM `information_schema`.`TABLES` `ist`
  WHERE `ist`.`TABLE_SCHEMA` = `db_name` AND
        `ist`.`ENGINE` = 'NDBCLUSTER';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET `v_finished` := TRUE;

  OPEN `all_tbl_cursor`;

  `get_tbls`: LOOP
    FETCH `all_tbl_cursor` INTO `tbl_name`;

    IF `v_finished` THEN
      BEGIN
        DECLARE CONTINUE HANDLER FOR 1243 BEGIN END;
        CLOSE `all_tbl_cursor`;
        DEALLOCATE PREPARE `stmt`;
        LEAVE `get_tbls`;
      END;
    END IF;

    SET @`sql` := CONCAT('ALTER TABLE `', `db_name`, '`.`', `tbl_name`, '` REORGANIZE PARTITION');
    PREPARE `stmt` FROM @`sql`;
    EXECUTE `stmt`;

    SET @`sql` := CONCAT('OPTIMIZE TABLE `', `db_name`, '`.`', `tbl_name`, '`');
    PREPARE `stmt` FROM @`sql`;
    EXECUTE `stmt`;
    SET @`sql` := NULL;
  END LOOP `get_tbls`;
END//

DELIMITER ;