Show tables by engine in MySQL

Question

How would I show all tables in MySQL with a given engine, e.g. InnoDB, MyISAM, FEDERATED?

score 139 · Accepted Answer · answered Nov 11 '09 at 21:20

139

Use INFORMATION_SCHEMA.TABLES table:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  WHERE engine = 'InnoDB'

answered Nov 11 '09 at 21:20

ChssPly76

99,456
24
206
195

4

You might also want the schema with that, so the query would look like: SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'InnoDB' – Dan Bowling Aug 16 '16 at 01:42

score 82 · Answer 2 · answered Sep 06 '12 at 05:01

82

If you want the results from a single database

SELECT TABLE_NAME FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'dbname' AND engine = 'InnoDB';

answered Sep 06 '12 at 05:01

Alvin

2,533
33
45

1

I marked this because you probably want to look at a certain schema only – Csaba Toth Nov 29 '16 at 23:04

MTK · Answer 3 · 2017-06-20T12:43:59.510

Other examples here.

All tables by engine (except system tables):

SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine 
FROM information_schema.TABLES 
WHERE ENGINE = 'MyISAM' -- or InnoDB or whatever
AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');

All tables except engine (except system tables):

SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine 
FROM information_schema.TABLES 
WHERE ENGINE != 'MyISAM' -- or InnoDB or whatever
AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');

score 1 · Answer 4 · answered Nov 26 '16 at 09:04

If some has problem and want to see in which DB is tables with specific engine

SELECT 
        (SELECT group_concat(TABLE_NAME) 
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = 'database1' 
            AND engine = 'MyIsam'
        ) as database1, 
        (SELECT group_concat(TABLE_NAME) 
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = 'database2' 
            AND engine = 'MyIsam'
        ) as database2,
        (SELECT group_concat(TABLE_NAME) 
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = 'database3' 
            AND engine = 'MyIsam'
         ) as database3;

Regards.

Show tables by engine in MySQL

4 Answers4