Mysql Alter Table Select all Tables
07 Mar 12 20:24
One minute read

In case you wanted for example to change all your MyISAM tables into InnoDB , and you didn’t want to go over the tables one by one, you can do the following queries.

First check that you’re doing it on the proper database.

USE information_schema;
SELECT TABLE_SCHEMA, TABLE_NAME 
  FROM TABLES 
 WHERE TABLE_SCHEMA = "your_db_name";

If it returns what you need you can then run:

SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ENGINE = INNODB; ") as MySQLCMD 
  FROM TABLES 
 WHERE TABLE_SCHEMA = "your_db_name";

« Back to posts