Change MySQL Engine for a bunch of tables (or all of them)

Prepare for another very technical / boring morning blog !

I needed to change an entire MySQL database from MyISAM to InnoDB (that is, change all tables in the schema). After Googling for how to do that in a bulk, I found... nothing (didn't look very thoroughly, though...)

I was contemplating doing it manually, as there weren't too many tables in my schema, but I finally caved in and the Engineer in me won and went looking for an our of the box solution.

This is what I came up with (don't laugh) :
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ENGINE = InnoDB;') FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '';

Run this from your MySQL command line, control-mouse-select the result (so that you can get just the text without the frame) and paste into your favorite MySQL client. Voila.

And the Geek Oscar this morning goes to....

Hope you find this useful