The answer is
SHOW CREATE TABLE <tablename>;
Less parseable but more readable than SHOW TABLE STATUS.
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_%';
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.
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
.
.
.
or just
show table status;
just that this will llist all tables on your database.
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.
SHOW CREATE TABLE <tablename>;
Less parseable but more readable than SHOW TABLE STATUS.
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;
or just
show table status;
just that this will llist all tables on your database.
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_%';
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.
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;
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.
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.
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 table status from database_name;
It will list all tables from the mentioned database.
Example output
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.
SHOW CREATE TABLE <tablename>;
Less parseable but more readable than SHOW TABLE STATUS.
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.
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.
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 table status from database_name;
It will list all tables from the mentioned database.
Example output
show table status where name=your_desired_table_name;
It will show the storage engine used by the mentioned table.
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.
Source: Stackoverflow.com