Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- 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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.