MySQueaL Resources

SHOW TABLE STATUS Statement

This MySQL statement displays status information on a set of tables from a database.

This MySQL statement displays status information on a set of tables from a MySQL 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 MySQL tables of the database unless the LIKE clause is used to limit the tables displayed by a naming pattern. Similarly, the WHERE clause may be used to refine the results set. As an alternative to this MySQL statement, you can use the utility mysqlshow with the --status option.

Here's an example of this MySQL statement using the LIKE clause:

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:

This example shows results for only one table, because a specific table name is given in the LIKE clause without the % wildcard. To find a group of tables, but to limit the results more, you can use the WHERE clause. An example of its use follows:

SHOW TABLE STATUS FROM workrequests 
WHERE Rows > 1000;

This example lists all MySQL tables from the given database that contain more than a thousand rows of data. Notice that we're using the field name Rows from the results set to limit the results. Any field name can be used in this way and multiple fields may be given, separated by the AND parameter of the WHERE clause.

As for the results themselves, most are obvious from their field name. The Row_format field can have a value of Compact, Compressed, Dynamic, Fixed, or Redundant. InnoDB tables are either Compact or Redundant. The Rows field gives an accurate count with MyISAM tables, but not with InnoDB.

The Data_length field gives the size of the data file associated with the table. Max_data_length is the maximum size allowed for the data file. These two values are estimates for MEMORY tables. The Auto_increment value shows the value for the column which uses AUTO_INCREMENT.

When used with views, this statement returns NULL values for almost all fields.

You can change some of these variables or MySQL table options using the MySQL statement, ALTER TABLE.