Repair (or optimize) all tables from a database

Posted on 23/12/2011 by admin

This stored routime fetches all tables from database, then executes an operation of REPAIR (you could also use OPTIMIZE command also) for each one. It operates on tables in database in which procedure has been defined.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE PROCEDURE `tools_repair_all_tables`()
LANGUAGE SQL
SQL SECURITY INVOKER
BEGIN
DECLARE endloop INT DEFAULT 0;
DECLARE tableName char(100);
DECLARE rCursor CURSOR FOR SELECT `TABLE_NAME` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`=DATABASE();
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET endloop=1;
OPEN rCursor;
FETCH rCursor INTO tableName;
WHILE endloop = 0 DO
SET @sql = CONCAT('REPAIR TABLE `', tableName, '` QUICK;');
PREPARE statement FROM @sql;
EXECUTE statement;
FETCH rCursor INTO tableName;
END WHILE;
CLOSE rCursor;
END

Categories: Mysql stored routines | Tags: automatic, database, mysql, repair, script, tables

About admin

Born in 1985 in Baia Mare, Maramures county in Romania. Studied at North University of Baia Mare, and obtained bachelor degree in 2011 in Computational Mathematic Applied in Informatics.

View all posts by admin→