12

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.

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.

  • Thanks @AbdulManaf. I didn't expect the first command would take time on the server. – James Wise Sep 17 '13 at 5:42
  • Yes,It should not take time. – Abdul Manaf Sep 17 '13 at 6:25
  • How to query to find all the tables in databases that are not InnoDB? – James Wise May 9 '14 at 3:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.