How to find the MySQL engine of a table

Question

Possible Duplicate:
How can I check MySQL engine type for a specific table?

What is the query to get the table engine other than show create table table_name and show status?

Answer 1

For one table:

SELECT table_name,engine FROM INFORMATION_SCHEMA.TABLES 
WHERE table_name='table_name';

For all the tables in the schema:

SELECT table_name,engine FROM INFORMATION_SCHEMA.TABLES 
WHERE table_schema='schema_name';

When logged in as the same schema:

SELECT table_name,engine FROM INFORMATION_SCHEMA.TABLES 
WHERE table_schema=DATABASE();

Answer 2

SHOW TABLE STATUS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html