Knowledge Walls

Ad Size: (333 X 90) px
John Peter
Pune, Maharashtra, India
How to check storage engine of a table in mysql?
594 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.
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