Name

SHOW TABLE STATUS

Synopsis

SHOW TABLE STATUS [FROM database] [LIKE 'pattern']

This statement displays status information on a set of tables from a database. To obtain the status of tables from a database other than the current default one, use the FROM clause. The results will include information on all of the tables of the database unless the LIKE clause is used to limit the tables displayed by a naming pattern.

SHOW TABLE STATUS FROM workrequests LIKE 'workreq'\G
*************************** 1. row ***************************
           Name: workreq
         Engine: MyISAM
        Version: 7
     Row_format: Dynamic
           Rows: 543
 Avg_row_length: 983
    Data_length: 534216
Max_data_length: 4294967295
   Index_length: 6144
      Data_free: 120
 Auto_increment: 5772
    Create_time: 2002-04-23 14:41:58
    Update_time: 2004-11-26 16:01:46
     Check_time: 2004-11-28 17:21:20
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

In this example, the number of tables is limited to one, because a specific table name is given in the LIKE clause without the % wildcard. Incidentally, the display here is not in the typical ASCII table format, because the statement ends with a \G instead of the usual semicolon. You can change some of these variables or table options using the ALTER TABLE statement.

Get MySQL in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial