Skip navigation links
MySQL 5.0 Reference Manual :: 19 INFORMATION_SCHEMA Tables :: 19.12 The INFORMATION_SCHEMA TABLES Table
19.13 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table »
Section Navigation      [Toggle]

19.12. The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG NULL
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:

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

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

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

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']
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Leslie I'Anson on February 11 2009 10:37am[Delete] [Edit]

My understanding of calculating database size is to add the size of the tables to the size of the indexes; database = table(s) + index(es).

SELECT concat( table_schema, '.', table_name ) table_name,
concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length,
concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length,
concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size
FROM information_schema.TABLES
ORDER BY data_length DESC;

I've completed some rudimentary tests which seem to confirm this, at least in the case of MySQL databases.

Feedback welcomed!

Posted by Aleksandar Kostadinov on October 12 2009 1:45pm[Delete] [Edit]

Better would be 'ORDER BY ( data_length + index_length ) DESC' to get proper ordering. As well you can skip the inner round in total_size calculation.
Additionally engine type should be observed. One would care about certain types of tables. For example memory or non-memory. Unfortunately I can't see a consistent way to tell how much real disk space is occupied by a database.

Thanks for the expression though!

Following should show size per database:
SELECT table_schema 'database',
concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size
FROM information_schema.TABLES
WHERE ENGINE=('MyISAM' || 'InnoDB' )
GROUP BY table_schema;

Posted by Kalina Detko on September 2 2010 3:41pm[Delete] [Edit]

TABLE_ROWS (and perhaps some other columns in INFORMATION_SCHEMA.TABLES) may get out of sync with the current table contents, but you can update it by running ANALYZE.

Posted by Naref Szolik on May 27 2012 1:03pm[Delete] [Edit]

UPDATE_TIME only stores values for a subset of the available table storage engines.

Posted by Chris Calender on March 7 2013 8:17pm[Delete] [Edit]

UPDATE_TIME works for MyISAM, not InnoDB.

From my tests, it updated the timestamp on both DML and DDL operations.

Add your own comment.