Identify table storage engine in MySQL database (schema)

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:
    • CSV
    • InnoDB
    • Memory
    • MyISAM
    • Archive
    • Blackhole
    • MRG_MyISAM
    • Federated

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

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