Additional languages
MySQL 5.1 Reference Manual :: 22 The INFORMATION_SCHEMA Database :: 22.2 The INFORMATION_SCHEMA TABLES Table
« 22.1 The INFORMATION_SCHEMA SCHEMATA Table
22.3 The INFORMATION_SCHEMA COLUMNS Table »
Section Navigation      [Toggle]

22.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 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. If table is temporary, then TABLE_TYPE = TEMPORARY. (There are no temporary views, so this is not ambiguous.)

  • For partitioned tables, beginning with MySQL 5.1.9, 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, beginning with MySQL 5.1.12, 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.

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

  • Beginning with MySQL 5.1.9, 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']
  [WHERE|AND table_name LIKE 'wild']

SHOW TABLES
  [FROM db_name]
  [LIKE 'wild']

User Comments

Posted by Jeff C on December 7 2005 10:03am[Delete] [Edit]

Note this minor change, coming from:
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-6.html

Changed default value of myisam_data_pointer_size from 4 to 6. This allows us to avoid table is full errors for most cases.


Add your own comment.