MySQL in a Nutshell, 2nd Edition

Name

SHOW TABLE STATUS

Synopsis

SHOW TABLE STATUS [FROM database] [LIKE 'pattern'|WHERE expression]

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