The Information Schema TABLES table provides metadata about tables in databases, including row counts, storage engines, and creation times.
The Information Schema table shows information about the various tables and views on the server.
The Information Schema table shows information about the various tables, excludingTEMPORARY tables, except for tables from the Information Schema database) and views on the server.
It contains the following columns:
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.
lists all tables in a database.
Example with temporary = 'y':
Returns a list of all tables in the database, ordered by size:
Returns information about a temporary table:
This page is licensed: CC BY-SA / Gnu FDL
AVG_ROW_LENGTH
Average row length in the table.
DATA_LENGTH
For , the index size, in pages, multiplied by the page size. For and , length of the data file, in bytes. For , 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 .
INDEX_LENGTH
Length of the index file.
DATA_FREE
Bytes allocated but unused. For 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 table.
AUTO_INCREMENT
Next value.
CREATE_TIME
Time the table was created. Some engines just return the ctime information from the file system layer here, in that case the value is not necessarily the table creation time but rather the time the file system metadata for it had last changed.
UPDATE_TIME
Time the table was last updated. On Windows, the timestamp is not updated on update, so MyISAM values will be inaccurate. In , 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
.
CHECKSUM
Live checksum value, if any.
CREATE_OPTIONS
Extra options.
TABLE_COMMENT
Table comment provided when MariaDB created the table.
MAX_INDEX_LENGTH
Maximum index length (supported by MyISAM and Aria tables).
TEMPORARY
Is set to "Y" for local temporary tables.
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, SYSTEM VERSIONED for system-versioned tables, SEQUENCE for sequences or TEMPORARY for local temporary tables.
ENGINE
VERSION
Version number from the table's .frm file
ROW_FORMAT
TABLE_ROWS
Number of rows in the table. Some engines, such as XtraDB and InnoDB may store an estimate.
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
...SELECT * FROM information_schema.tables WHERE temporary='y'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: information_schema
TABLE_NAME: INNODB_FT_DELETED
TABLE_TYPE: SYSTEM VIEW
ENGINE: MEMORY
VERSION: 11
ROW_FORMAT: Fixed
TABLE_ROWS: NULL
AVG_ROW_LENGTH: 9
DATA_LENGTH: 0
MAX_DATA_LENGTH: 9437184
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2020-11-17 21:54:02
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: max_rows=1864135
TABLE_COMMENT:
MAX_INDEX_LENGTH: 0
TEMPORARY: Y
...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 |
...CREATE TEMPORARY TABLE foo.t1 (a INT);
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='foo' AND TEMPORARY='y'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: foo
TABLE_NAME: t1
TABLE_TYPE: TEMPORARY
...
TEMPORARY: Y