Skip navigation links

Additional languages
MySQL 6.0 Reference Manual :: 26 INFORMATION_SCHEMA Tables :: 26.2 The INFORMATION_SCHEMA TABLES Table
26.3 The INFORMATION_SCHEMA COLUMNS Table »
Section Navigation      [Toggle]

26.2. The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

INFORMATION_SCHEMA Name SHOW Name Remarks
TABLE_CATALOG   NULL
TABLE_SCHEMA Table_...  
TABLE_NAME Table_...  
TABLE_TYPE    
ENGINE Engine MySQL extension
VERSION Version The version number of the table's .frm file, MySQL extension
ROW_FORMAT Row_format MySQL extension
TABLE_ROWS Rows MySQL extension
AVG_ROW_LENGTH Avg_row_length MySQL extension
DATA_LENGTH Data_length MySQL extension
MAX_DATA_LENGTH Max_data_length MySQL extension
INDEX_LENGTH Index_length MySQL extension
DATA_FREE Data_free MySQL extension
AUTO_INCREMENT Auto_increment MySQL extension
CREATE_TIME Create_time MySQL extension
UPDATE_TIME Update_time MySQL extension
CHECK_TIME Check_time MySQL extension
TABLE_COLLATION Collation MySQL extension
CHECKSUM Checksum MySQL extension
CREATE_OPTIONS Create_options MySQL extension
TABLE_COMMENT Comment MySQL extension

Notes:

  • TABLE_SCHEMA and TABLE_NAME are a single field in a SHOW display, for example Table_in_db1.

  • TABLE_TYPE should be BASE TABLE or VIEW. Currently, the TABLES table does not list TEMPORARY tables.

  • For partitioned tables, the ENGINE column shows the name of the storage engine used by all partitions. (Previously, this column showed PARTITION for such tables.)

  • The TABLE_ROWS column is NULL if the table is in the INFORMATION_SCHEMA database. For InnoDB tables, the row count is only a rough estimate used in SQL optimization.

  • For tables using the NDBCLUSTER storage engine, the DATA_LENGTH column reflects the true amount of storage for variable-width columns. (See Bug#18413.)

    Note

    Because MySQL Cluster allocates storage for variable-width columns in 10-page extents of 32 kilobytes each, space usage for such columns is reported in increments of 320 KB.

  • Beginning with MySQL 6.0.5, the DATA_FREE column shows the free space in bytes for InnoDB tables. (Bug#32440)

  • We have nothing for the table's default character set. TABLE_COLLATION is close, because collation names begin with a character set name.

  • The CREATE_OPTIONS column shows partitioned if the table is partitioned.

The following statements are equivalent: 

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLES
  FROM db_name
  [LIKE 'wild']