How can I verify from the command line that all of the tables in my DB are InnoDB?

Question

How can I quickly verify from the command line that all of the tables in my DB are InnoDB?

score 5 · Accepted Answer · edited May 18 '11 at 14:58

Count tables from each storage engine

SELECT COUNT(1) table_count,engine 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema','mysql') 
GROUP BY engine;

or to check each database's storage engine count

SELECT COUNT(1) table_count,table_schema,engine 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema','mysql') 
GROUP BY table_schema,engine;

or get a count all non-InnoDB tables ( should be 0 )

SELECT COUNT(1) table_count 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema','mysql') 
AND engine <> 'InnoDB';

To List Tables Names That are Not InnoDB and What Database the Table in Stored

SELECT table_schema,table_name 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema','mysql') 
AND engine <> 'InnoDB';

is there a way to list tables (tale names) by storage engine as well? Assuming he has one or more tables that need to be converted it would be helpful to know what they are :) — voretaq7, Feb 15 '11 at 22:24

score 0 · Answer 2 · edited Jul 13 '12 at 17:21

0

mysql> USE xyz;
mysql> SHOW TABLE STATUS;

It gives you the list of all tables and their engines.

edited Jul 13 '12 at 17:21

jscott

24,484
8
79
100

answered Feb 17 '11 at 16:53

Kourosh Samia

321
1
5
12

How can I verify from the command line that all of the tables in my DB are InnoDB?

2 Answers2