Linux Ask!

Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.

Jul 132011

Alter all tables in MySQL


To alter all the tables in a given database in MySQL, e.g. change the storage engine, you can first generate the SQL statements using following method.

mysql> select CONCAT("Alter Table `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ENGINE = InnoDB") AS cmd from TABLES where TABLE_SCHEMA = "test";

It will print out the followings:

| cmd                                                            |
| Alter Table `test`.`foo`  ENGINE = InnoDB                      | 
| Alter Table `test`.`bar`  ENGINE = InnoDB                      | 

Then you can execute the above commands manually.

 Leave a Reply



You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>