Table of Contents for

MySQL in a Nutshell, 2nd Edition

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. Similarly, the WHERE clause may be used to refine the results set. As an alternative to this statement, you can use the utility mysqlshow with the --status option, as described in Chapter 16. Here’s an example of this 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. Here is an example:

SHOW TABLE STATUS FROM workrequests 
WHERE Rows > 1000;

This example lists all tables from the given database that contain more than 1,000 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 names. 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 datafile associated with the table. Max_data_length is the maximum size allowed for the datafile. These two values are estimates for MEMORY tables. The Auto_increment value shows the value for the column that uses AUTO_INCREMENT.

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

You can change some of these variables or table options using the ALTER TABLE statement in Chapter 5.