List InnoDB tables in MySQL database

Article for: MySQL

The query below lists all InnoDB tables in all user databases. To list MyISAM tables from current database use this query.

Query

select table_schema as database_name,
       table_name
from information_schema.tables tab
where engine = 'InnoDB'
      and table_type = 'BASE TABLE'
      and table_schema not in ('information_schema', 'sys',
                               'performance_schema','mysql')
      -- and table_schema = 'your database name' 
order by table_schema,
         table_name;

Columns

  • database_name - database (schema) name
  • table_name - table name

Rows

  • One row: represents one table in a database (schema)
  • Scope of rows: all tables which uses InnoDB as storage engine in all databases (schemas)
  • Ordered by: database name, table name

Sample results

0
There are no comments. Click here to write the first comment.