Show tables by engine in MySQL

Question

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

Answer 1

Use INFORMATION_SCHEMA.TABLES table:

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

Answer 2

If you want the results from a single database

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