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.
