How to find all tables use particular storage engine on MySQL

MySQL supports various table engines, including MyISAM, InnoDB, MEMORY, and NDB. Finding which engine a table uses may seem complicated, but it is straightforward. Query the INFORMATION_SCHEMA database, which already stores information about each table’s engine. The command uses ordinary SQL syntax, with nothing special. To find tables using the InnoDB engine, run:

mysql> SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

To find tables using the MyISAM engine, run:

mysql> SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';

The commands above differ only in engine = ‘xxx’. To check another engine, replace the engine name with the one you want. Simple, right? Example output:

+--------------------+-------------------------------+
| table_schema       | table_name                    |
+--------------------+-------------------------------+
| information_schema | COLUMNS                       |
| information_schema | EVENTS                        |
| information_schema | PARAMETERS                    |
| information_schema | PARTITIONS                    |
| information_schema | PLUGINS                       |
| information_schema | PROCESSLIST                   |
| information_schema | ROUTINES                      |
                    ...
| information_schema | TRIGGERS                      |
| information_schema | VIEWS                         |
| mysql              | columns_priv                  |
| mysql              | help_keyword                  |
| mysql              | help_relation                 |
| mysql              | tables_priv                   |
+--------------------+-------------------------------+
25 rows in set (0.01 sec)

*Note table_schema is the database name. table_name is the table name. * To see which storage engines your MySQL supports, use SHOW ENGINES\G. How to convert the storage engine from MyISAM to InnoDB