Get metadata for tables. View details like engine type, row count, data length, and create time for tables in a database.
SHOW TABLE STATUS works like , but provides more extensive information about each table.
SHOW TABLE STATUS works like , but provides more extensive information about each table. Only non-TEMPORARY tables are shown.
The LIKE clause, if present on its own, indicates which table names to match. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in .
The following information is returned:
Similar information can be found in the table as well as by using :
For views, all columns in SHOW TABLE STATUS are NULL except 'Name' and 'Comment'
This page is licensed: GPLv2, originally from
SHOW TABLE STATUS [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]Data_length
For , the index size, in pages, multiplied by the page size. For and , length of the data file, in bytes. For , the approximate allocated memory.
Max_data_length
Maximum length of the data file, ie the total number of bytes that could be stored in the table. Not used in .
Index_length
Length of the index file.
Data_free
Bytes allocated but unused. For InnoDB tables in a shared tablespace, the free space of the shared tablespace with small safety margin. An estimate in the case of partitioned tables - see the table.
Auto_increment
Next value.
Create_time
Time the table was created. Some engines just return the ctime information from the file system layer here, in that case the value is not necessarily the table creation time but rather the time the file system metadata for it had last changed.
Update_time
Time the table was last updated. On Windows, the timestamp is not updated on update, so MyISAM values will be inaccurate. In , if shared tablespaces are used, will be NULL, while buffering can also delay the update, so the value will differ from the actual time of the last UPDATE, INSERT or DELETE.
Check_time
Time the table was last checked. Not kept by all storage engines, in which case will be NULL.
Collation
.
Checksum
Live checksum value, if any.
Create_options
Extra options.
Comment
Table comment provided when MariaDB created the table.
Max_index_length
Maximum index length (supported by MyISAM and Aria tables).
Temporary
Until , placeholder to signal that a table is a temporary table and always "N", except "Y" for generated information_schema tables and NULL for views. From , will also be set to "Y" for local temporary tables.
Name
Table name.
Engine
Table storage engine.
Version
Version number from the table's .frm file.
Row_format
Rows
Number of rows in the table. Some engines, such as InnoDB may store an estimate.
Avg_row_length
Average row length in the table.
mariadb-show --status db_nameSHOW TABLE STATUS\G
*************************** 1. row ***************************
Name: bus_routes
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-05-24 11:17:46
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: