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?

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.

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

Name
Email address
Website
Comments
	Notify me of follow-up comments via email.

Similar Tutorials:

• Fast, parallel restore from SQL dumps (mysqldump) for MySQL
• Unique primary keys(PKs) for MySQL using Redis
• How to set innodb_flush_log_at_timeout in MySQL 5.6?
• SQL: Union and Union All
• MySQL open files limit: open-files-limit VS open_files_limit on Linux

Tutorials in 'Database Management Systems > MySQL' (more):

• How to set default values for MySQL JSON columns
• How to delete rows in table when PK is referenced in another table
• Unique primary keys(PKs) for MySQL using Redis
• How to rename MySQL DB by moving tables
• How to set innodb_flush_log_at_timeout in MySQL 5.6?

Recent Tutorials:

• How to set default values for MySQL JSON columns
• How to delete rows in table when PK is referenced in another table
• Unique primary keys(PKs) for MySQL using Redis
• How to rename MySQL DB by moving tables
• How to compile and install Varnish-cache on Linux