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';

Please feel free to use the comments form below if you have any questions or need more explanation on anything.

Tags

mysql, database, opensource, myisam, storage-engine,

Popular Searches

linux, php, mysql, ubuntu, mysql mysql, tools, install mysql, gearman, source code, java

more>>

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

 

Comment
 

 
| Members | Support Us | Using this site means you accept its terms | Contact Us | Site Map |