21.23 The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG def
TABLE_SCHEMATable_... 
TABLE_NAMETable_... 
TABLE_TYPE  
ENGINEEngineMySQL extension
VERSIONVersionThe version number of the table's .frm file, MySQL extension
ROW_FORMATRow_formatMySQL extension
TABLE_ROWSRowsMySQL extension
AVG_ROW_LENGTHAvg_row_lengthMySQL extension
DATA_LENGTHData_lengthMySQL extension
MAX_DATA_LENGTHMax_data_lengthMySQL extension
INDEX_LENGTHIndex_lengthMySQL extension
DATA_FREEData_freeMySQL extension
AUTO_INCREMENTAuto_incrementMySQL extension
CREATE_TIMECreate_timeMySQL extension
UPDATE_TIMEUpdate_timeMySQL extension
CHECK_TIMECheck_timeMySQL extension
TABLE_COLLATIONCollationMySQL extension
CHECKSUMChecksumMySQL extension
CREATE_OPTIONSCreate_optionsMySQL extension
TABLE_COMMENTCommentMySQL extension

Notes:

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']