The query below lists tables for all databases (schemas) and identifies their storage engine.

Query

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

Columns

• table_schema - database (schema) name
• table_name - table name
• engine - table storage engine. Possible values:
  • Archive
  • Aria
  • Blackhole
  • CassandraSE
  • ColumnStore
  • CONNECT
  • CSV
  • FederatedX
  • InnoDB
  • Memory
  • MRG_MyISAM
  • Mroonga
  • MyISAM
  • MyRocks
  • OQGRAPH
  • Spider
  • TokuDB
  • XtraDB

Rows

• One row: represents one table in a database (schema)
• Scope of rows: all tables in the databases (schemas)
• Ordered by: database (schema) and table name

Sample results