Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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