|
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
|
-- 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'); |
