Show tables with the engine in MySQL

Question

How can I show all tables in MySQL with a given engine, for example. InnoDB, MyISAM, FEDERATED?

Answer 1

Use the INFORMATION_SCHEMA.TABLES table:

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

Answer 2

If you want to get results from one database

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

Answer 3

Other examples are here.

All tables using the 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 the mechanism (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'); 

Answer 4

If someone has a problem and you want to see which database has tables with a 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; 

Sincerely.