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.

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.

Comments