W3cubDocs

Information Schema TABLES Table

The Information Schema table shows information about the various non-TEMPORARY tables and views on the server.

It contains the following columns:

Column	Description
`TABLE_CATALOG`	Always `def`.
`TABLE_SCHEMA`	Database name.
`TABLE_NAME`	Table name.
`TABLE_TYPE`	One of `BASE TABLE` for a regular table, `VIEW` for a view, `SYSTEM VIEW` for Information Schema tables or `SYSTEM VERSIONED` for system-versioned tables.
`ENGINE`	Storage Engine.
`VERSION`	Version number from the table's .frm file
`ROW_FORMAT`	Row format (see InnoDB, Aria and MyISAM row formats).
`TABLE_ROWS`	Number of rows in the table. Some engines, such as XtraDB and InnoDB may store an estimate.
`AVG_ROW_LENGTH`	Average row length in the table.
`DATA_LENGTH`	For InnoDB/XtraDB, the index size, in pages, multiplied by the page size. For Aria and MyISAM, length of the data file, in bytes. For MEMORY, the approximate allocated memory.
`MAX_DATA_LENGTH`	Maximum length of the data file, ie the total number of bytes that could be stored in the table. Not used in XtraDB and InnoDB.
`INDEX_LENGTH`	Length of the index file.
`DATA_FREE`	Bytes allocated but unused. For InnoDB tables in a shared tablespace, the free space of the shared tablespace with small safety margin. An estimate in the case of partitioned tables - see the `PARTITIONS` table.
`AUTO_INCREMENT`	Next AUTO_INCREMENT value.
`CREATE_TIME`	Time the table was created.
`UPDATE_TIME`	Time the table was last updated. On Windows, the timestamp is not updated on update, so MyISAM values will be inaccurate. In InnoDB, if shared tablespaces are used, will be NULL, while buffering can also delay the update, so the value will differ from the actual time of the last `UPDATE`, `INSERT` or `DELETE`.
`CHECK_TIME`	Time the table was last checked. Not kept by all storage engines, in which case will be `NULL`.
`TABLE_COLLATION`	Character set and collation.
`CHECKSUM`	Live checksum value, if any.
`CREATE_OPTIONS`	Extra `CREATE TABLE` options.
`TABLE_COMMENT`	Table comment provided when MariaDB created the table.

Although the table is standard in the Information Schema, all but TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE and VERSION are MySQL and MariaDB extensions.

SHOW TABLES lists all tables in a database.

Examples

SELECT * FROM information_schema.TABLES\G
...
*************************** 175. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: xx4
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 1970324836974591
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2013-10-31 14:45:35
    UPDATE_TIME: 2013-10-31 14:45:35
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: key_block_size=46
  TABLE_COMMENT: 
*************************** 176. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: xx5
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2013-11-11 09:54:11
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: key_block_size=46
  TABLE_COMMENT: 
...

View Tables in Order of Size

Returns a list of all tables in the database, ordered by size:

SELECT table_schema as `DB`, table_name AS `Table`, 
  ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` 
  FROM information_schema.TABLES 
  ORDER BY (data_length + index_length) DESC;

+--------------------+---------------------------------------+-----------+
| DB                 | Table                                 | Size (MB) |
+--------------------+---------------------------------------+-----------+
| wordpress          | wp_simple_history_contexts            |      7.05 |
| wordpress          | wp_posts                              |      6.59 |
| wordpress          | wp_simple_history                     |      3.05 |
| wordpress          | wp_comments                           |      2.73 |
| wordpress          | wp_commentmeta                        |      2.47 |
| wordpress          | wp_simple_login_log                   |      2.03 |
...

Information Schema TABLES Table

Examples

View Tables in Order of Size

See Also