Knowledge Walls
John Peter
Pune, Maharashtra, India
How to check storage engine of a table in mysql?
725 Views
SHOW TABLE STATUS 
SHOW TABLE STATUS is a mysql query to check engine type, auto_increment next value, no of records in table, etc.,

Below Information are available about all tables in current database.
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
Collation
Checksum
Create_options
Comment
Examples.
-- To see all table status
SHOW TABLE STATUS;

-- Do where clauses filtering
SHOW TABLE STATUS WHERE name = "table-name";
SHOW TABLE STATUS WHERE rows > 10000;
To check all tables engine type across all databases?
-- To get which are all the tables are Innodb across all db's
SELECT
    *
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    ENGINE = "InnoDB";


-- To see all engine types across two db's
SELECT
    *
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA in ('db_name1','db_name2');
Previous Topics
Previous lessons of current book.
Computer software engineer articles of One day One Thing to Know
Best Lessons of "One day One Thing to Know"
Top lessons which are viewed more times.
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details