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

SHOW TABLE STATUS WHERE Name = 'xxx'

This will give you (among other things) an Engine column, which is what you want.

To show a list of all the tables in a database and their engines, use this SQL query:

SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'dbname';

Replace dbname with your database name.

SHOW CREATE TABLE <tablename>;

Less parseable but more readable than SHOW TABLE STATUS.

Or just run this:

show table status;

This will list all tables and related details on your database.

Bit of a tweak to Jocker's response (I would post as a comment, but I don't have enough karma yet):

SELECT TABLE_NAME, ENGINE
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = 'database' AND ENGINE IS NOT NULL;

This excludes MySQL views from the list, which don't have an engine.

SHOW CREATE TABLE <tablename>\G

will format it much nicer compared to the output of

SHOW CREATE TABLE <tablename>;

The \G trick is also useful to remember for many other queries/commands.

show table status from database_name;

It will list all tables from the mentioned database. Example output

https://i.stack.imgur.com/8yVDn.png

show table status where name=your_desired_table_name;

It will show the storage engine used by the mentioned table.

mysqlshow -i <database_name>

will show the info for all tables of a specific database.

mysqlshow -i <database_name> <table_name> 

will do so just for a specific table.

If you're using MySQL Workbench, right-click a table and select alter table.

In that window you can see your table Engine and also change it.

https://i.stack.imgur.com/zc2Nx.png

Yet another way, perhaps the shortest to get status of a single or matched set of tables:

SHOW TABLE STATUS LIKE 'table';

You can then use LIKE operators for example:

SHOW TABLE STATUS LIKE 'field_data_%';

If you are a linux user:

To show the engines for all tables for all databases on a mysql server, without tables information_schema, mysql, performance_schema:

less < <({ for i in $(mysql -e "show databases;" | cat | grep -v -e Database-e information_schema -e mysql -e performance_schema); do echo "--------------------$i--------------------";  mysql -e "use $i; show table status;"; done } | column -t)

You might love this, if you are on linux, at least.

Will open all info for all tables in less, press -S to chop overly long lines.

Example output:

--------------------information_schema--------------------
Name                                                        Engine              Version  Row_format  Rows   Avg_row_length  Data_length  Max_data_length     Index_length  Data_free  Auto_increment  Create_time  Update_time  Check_time  C
CHARACTER_SETS                                              MEMORY              10       Fixed       NULL   384             0            16434816            0             0          NULL            2015-07-13   15:48:45     NULL        N
COLLATIONS                                                  MEMORY              10       Fixed       NULL   231             0            16704765            0             0          NULL            2015-07-13   15:48:45     NULL        N
COLLATION_CHARACTER_SET_APPLICABILITY                       MEMORY              10       Fixed       NULL   195             0            16357770            0             0          NULL            2015-07-13   15:48:45     NULL        N
COLUMNS                                                     MyISAM              10       Dynamic     NULL   0               0            281474976710655     1024          0          NULL            2015-07-13   15:48:45     2015-07-13  1
COLUMN_PRIVILEGES                                           MEMORY              10       Fixed       NULL   2565            0            16757145            0             0          NULL            2015-07-13   15:48:45     NULL        N
ENGINES                                                     MEMORY              10       Fixed       NULL   490             0            16574250            0             0          NULL            2015-07-13   15:48:45     NULL        N
EVENTS                                                      MyISAM              10       Dynamic     NULL   0               0            281474976710655     1024          0          NULL            2015-07-13   15:48:45     2015-07-13  1
FILES                                                       MEMORY              10       Fixed       NULL   2677            0            16758020            0             0          NULL            2015-07-13   15:48:45     NULL        N
GLOBAL_STATUS                                               MEMORY              10       Fixed       NULL   3268            0            16755036            0             0          NULL            2015-07-13   15:48:45     NULL        N
GLOBAL_VARIABLES                                            MEMORY              10       Fixed       NULL   3268            0            16755036            0             0          NULL            2015-07-13   15:48:45     NULL        N
KEY_COLUMN_USAGE                                            MEMORY              10       Fixed       NULL   4637            0            16762755            0 

.
.
.

If you are a GUI guy and just want to find it in PhpMyAdmin, than pick the table of your choice and head over the Operations tab >> Table options >> Storage Engine. You can even change it from there using the drop-down options list.

PS: This guide is based on version 4.8 of PhpMyAdmin. Can't guarantee the same path for very older versions.

Apart from examples showed in previous entries, you can also get that from information_schema db with standard query as follows :

use information_schema;

select NAME from INNODB_TABLES where NAME like "db_name%";

go to information_schema database there you will find 'tables' table then select it;

Mysql>use information_schema; Mysql> select table_name,engine from tables;