I would appreciate if anyone could help me of what's the proper command in MySQL to query all database for having "MyISAM" engine. I need to convert all DB & tables from MyISAM to InnoDB.
12
23
Below is the Query to find all the tables which have MyISAM Engine
SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');
Above Query will list all the tables having MyISAM Engine.
For how to convert your existing MyISAM tables to InnoDB
Below is the Query that will Return ALTER Statements to convert existing MyISAM Tables to InnoDB.
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`',TABLE_NAME, '` ENGINE = InnoDB;') FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');
You can execute these statements to convert engines.
answered Sep 17 '13 at 4:56
-
Thanks @AbdulManaf. I didn't expect the first command would take time on the server. – James Wise Sep 17 '13 at 5:42
-
-
How to query to find all the tables in databases that are not InnoDB? – James Wise May 9 '14 at 3:24
