Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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';
share|improve this answer
    
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
    
Just updated my answer to list the Non-InnoDB tables –  RolandoMySQLDBA Feb 15 '11 at 22:27
mysql> USE xyz;
mysql> SHOW TABLE STATUS;

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.