SHOW TABLE STATUS

You are viewing an old version of this article. View the current version here.

Syntax

SHOW TABLE STATUS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Description

SHOW TABLE STATUS works like SHOW TABLES, but provides more extensive information about each non-TEMPORARY table.

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 Extended SHOW.

The following information is returned:

ColumnDescription
NameTable name.
EngineTable storage engine.
VersionVersion number from the table's .frm file.
Row_formatRow format (see InnoDB, Aria and MyISAM row formats).
RowsNumber of rows in the table. Some engines, such as XtraDB and InnoDB may store an estimate.
Avg_row_lengthAverage row length in the table.
Data_lengthFor InnoDB/XtraDB, the index size, in pages, multiplied by the page size. For Aria and MyISAM, length of the data file, in bytes. For MEMORY, the approximate allocated memory.
Max_data_lengthMaximum length of the data file, ie the total number of bytes that could be stored in the table. Not used in XtraDB and InnoDB.
Index_lengthLength of the index file.
Data_freeBytes 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 PARTITIONS table.
Auto_incrementNext AUTO_INCREMENT value.
Create_timeTime the table was created.
Update_timeTime the table was last updated. On Windows, the timestamp is not updated on update, so MyISAM values will be inaccurate. In InnoDB, 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_timeTime the table was last checked. Not kept by all storage engines, in which case will be NULL.
CollationCharacter set and collation.
ChecksumLive checksum value, if any.
Create_optionsExtra CREATE TABLE options.
CommentTable comment provided when MariaDB created the table.
Max_index_lengthMaximum index length (supported by MyISAM and Aria tables). Added in MariaDB 10.3.5.
TemporaryPlaceholder to signal that a table is a temporary table. Currently always "N", except "Y" for generated information_schema tables and NULL for views. Added in MariaDB 10.3.5.

Similar information can be found in the information_schema.TABLES table as well as by using mysqlshow:

mysqlshow --status db_name

Example

show 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:

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.