Information Schema TABLES Table
You are viewing an old version of this article. View
the current version here.
Contents
The Information Schema table shows information about the various non-TEMPORARY
tables (except tabes from Information Schema
database) 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. |
MAX_INDEX_LENGTH | Maximum index length (supported by MyISAM and Aria tables). Added in MariaDB 10.3.5. |
TEMPORARY | Placeholder to signal that a table is a temporary table. Currently always "N", except "Y" for generated information_schema tables and NULL for views. Added in MariaDB 10.3.5. |
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
MariaDB [test]> select * from information_schema.tables where table_schema='test'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: xx5 TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic 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: 2020-11-18 15:57:10 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: latin1_swedish_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: MAX_INDEX_LENGTH: 0 TEMPORARY: N *************************** 2. 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: 2020-11-18 15:56:57 UPDATE_TIME: 2020-11-18 15:56:57 CHECK_TIME: NULL TABLE_COLLATION: latin1_swedish_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: MAX_INDEX_LENGTH: 17179868160 TEMPORARY: N 2 rows in set (0.001 sec) ...
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 | ...
See Also
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.