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

Category:

Tutorials > Database Management Systems > MySQL > Tips and Tricks

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 do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

Comments (write a comment):

i think table_type should be replace by engine in the above statements. Posted by: chandu on Dec 14, 2011

@chandu: thanks for pointing that out, I have updated the post now.

Thanks. Posted by: shahryar on Dec 14, 2011

first query still a TABLE_TYPE instead of ENGINE Posted by: Duude on Jun 06, 2017

leave a comment on tutorial leave a comment