Skip to content

Instantly share code, notes, and snippets.

@tbreuss
Last active February 23, 2016 10:44
Show Gist options
  • Save tbreuss/708d930ae1245a478c28 to your computer and use it in GitHub Desktop.
Save tbreuss/708d930ae1245a478c28 to your computer and use it in GitHub Desktop.
This is a stored procedure that loops through all tables of a database and enables indexes
DELIMITER $$
DROP PROCEDURE IF EXISTS procEnableKeysOnAllTables $$
CREATE PROCEDURE procEnableKeysOnAllTables()
BEGIN
DECLARE table_name VARCHAR(255);
DECLARE end_of_tables INT DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_schema = DATABASE() AND t.table_type='BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1;
OPEN cur;
tables_loop: LOOP
FETCH cur INTO table_name;
IF end_of_tables = 1 THEN
LEAVE tables_loop;
END IF;
SET @s = CONCAT('ALTER TABLE `', table_name, '` ENABLE KEYS');
PREPARE stmt FROM @s;
EXECUTE stmt;
END LOOP;
CLOSE cur;
END $$
DELIMITER ;
call procEnableKeysOnAllTables;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment