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 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.

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:

• How to set innodb_flush_log_at_timeout in MySQL 5.6?
• The server requested authentication method unknown to the client [phpmyadmin]
• MySQL tables statistics (size, engine, no. of rows etc.)
• Fast, parallel restore from SQL dumps (mysqldump) for MySQL
• MySQL Performance Optimization Part I

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

• What should be monitored on a busy Database/MysQL server?
• How to speed up a Mysql replica?
• The server requested authentication method unknown to the client [phpmyadmin]
• How to set default values for MySQL JSON columns
• How to delete rows in table when PK is referenced in another table

Recent Tutorials:

• How to create and maintain a fixed length queue in dart?
• What should be monitored on a busy Database/MysQL server?
• Options to store data locally in a flutter app
• Dart - x positional argument(s) expected, but 0 found.
• ?? - the null-coalescing operator in Dart