• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Find which tables use INNODB with MySQL

Find which tables use INNODB with 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.

Check Out These Related posts:

  1. How to tell which storage engine a MySQL table uses
  2. MySQL utility commands
  3. PHP script to convert MySQL tables to a new storage engine
  4. Check if a MySQL table exists

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap