

- Welcome to Geeksww.com
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.
tags cloud
popular searches
free download for mysql database server 5.1.5, bison, gearman, install cairo, php, java, install mysql, laptop, linux, mysql, source code, mysql initialization, mysql mysql, tools, ubuntu
Similar Tutorials:
- Download, configure, compile, and install MySQL 5.5 from source code on Linux
- MySQL error while dropping databases
- Initializing MySQL database after installation
- opensource-db-tools - A set of open source tools for MySQL
- MySQL tables statistics (size, engine, no. of rows etc.)
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?
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