How to find all tables of a particular storage engine in MySQL?

MySQL supports several storage engines with different features and functions. If you want to find out what tables are using a particular storage engine in MySQL instance then run these simple queries in a MySQL command line interface.

Note: MySQL information_schema database exists in MySQL version 5 and above.

mysql> use information_schema;
mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM `TABLES` WHERE TABLE_TYPE LIKE 'myisam';

The query above outputs all tables in a database that were created with a MyISAM storage engine

You can easily restrict the output of query above to include tables in a particular database schema on the server

mysql> use information_schema;
mysql> SELECT TABLE_NAME FROM `TABLES` WHERE TABLE_TYPE LIKE 'myisam' AND TABLE_SCHEMA LIKE 'database_name';

The query will now list all tables in its output that were created using MyISAM storage engine and that exist in database_name database on the server

Note: Please replace TABLE_TYPE with ENGINE in queries above in WHERE clause for newer MySQL versions. Example

mysql> use information_schema;
mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM `TABLES` WHERE ENGINE LIKE 'myisam';

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production.