Repair all tables in MySQL

8 September 2010

In mysql you can repair a table using:

repair table mytable;

How do you repair all the tables in a database? There’s no command for that, so you’ll need to repair each one individually.

That’s quite tedious, so here’s a bit of help in MySQL 5.

select concat('repair table ', table_name, ';') from information_schema.tables where table_schema='mydatabase';

Change mydatabase to the name of your database.

This gives you a load of repair table statements. They’re formatted in a table with vertical bars, so copy the whole lot, paste in to a text editor or word processor, do a find and replace: find “|” replace with nothing. Don’t worry about the spaces.

Copy and paste that lot in to your mysql command prompt. Job done.

Filed under: Uncategorized — Scott @ 1:08 pm


  1. Hello mate!
    Wow.. that’s a really cool and useful tip.
    Thanks for sharing it.
    MySQL has many nice features, indeed.

    Comment by rod — 25 September 2010 @ 2:50 pm

  2. it’s great tips, thanks, that help me a lot

    Comment by Gun — 5 December 2010 @ 3:16 am

  3. It’s even easier if mysql is ran with the options -N (skip column names in results) and -s (silent)

    Just copy and paste.

    Thanks for the tip though ! :)

    Comment by Dam — 27 June 2012 @ 4:11 pm

RSS feed for comments on this post.

Leave a comment