How to check if MySQL table is UTF-8 and has storageEngine InnoDB?

Question

Googling around just finds instructions for changing from one format to another, but I can't seem to find how exactly to make sure which of these I have first.

How can I:

1. Check what character encoding a table has?
2. Check what storage engine a table uses?
3. Check if all tables are certain encoding?
4. Check if all tables have a certain storage engine?

Answer 1

You can use information_schema in order to know the engine of each table.

select table_name,engine 
from information_schema.tables
where table_schema = 'your_database'

For the encoding you can use

show create table table_name

or, even better

select 
c.character_set_name 
from information_schema.tables as t,
     information_schema.collation_character_set_applicability as c
where c.collation_name = t.table_collation
and t.table_schema = "your_db"
and t.table_name = "table_name";