Find which tables use INNODB with MySQL
Posted June 10th, 2009 in MySql
I recently needed to find which tables across all MySQL databases on a particular server used INNODB as the storage engine. This short post shows the query required to do this.
The SQL to do this queries the INFORMATION_SCHEMA and is as follows:
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';
table_schema is the name of the database.
table_name is (obviously) the name of the table.
This may take some time to run especially if there are a lot of databases and/or tables. Some example data returned from the above query looks like this where there are two databases (test1 and test2) with a couple of tables each that use the INNODB storage engine:
+-------------------+----------------------------------------+ | table_schema | table_name | +-------------------+----------------------------------------+ | test1 | sessions | | test1 | users | | test2 | products | | test2 | categories | +-------------------+----------------------------------------+ 4 rows in set (2.08 sec)
In my next MySQL post I'll look at how the INNODB storage engine by default puts all the tables into a single file called ibdata1 and how you can change the default to have a single file per table in the same way that the MyISAM storage engine does.
Related posts:
- Get a MySQL table structure from the INFORMATION_SCHEMA (Thursday, August 6th 2009)
- Use one file per table with MySQL's INNODB storage engine (Wednesday, June 17th 2009)
- How to change the storage engine a MySQL table uses (Saturday, January 17th 2009)
- How to tell which storage engine a MySQL table uses (Tuesday, January 13th 2009)
Share or Bookmark
Share or Bookmark this page using the following services. You will need to have an account with the selected service in order to post links or bookmark this page.
Subscribe or Follow
Subscribe via RSS or email, or follow me on Facebook or Twitter below. The RSS icon takes you through to Feedburner where you can select the service or application to use.
