The INNODB_SYS_TABLES
table provides
metadata about InnoDB
tables, equivalent to the
information from the SYS_TABLES
table in the
InnoDB
data dictionary.
For related usage information and examples, see Section 14.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.
The INNODB_SYS_TABLES
table has these
columns:
TABLE_ID
An identifier for the
InnoDB
table. This value is unique across all databases in the instance.NAME
The name of the table, preceded by the schema (database) name where appropriate; for example
test/t1
. Names of databases and user tables are in the same case as they were originally defined, possibly influenced by thelower_case_table_names
setting.FLAG
A numeric value that represents bit-level information about table format and storage characteristics.
N_COLS
The number of columns in the table. The number reported includes three hidden columns that are created by
InnoDB
(DB_ROW_ID
,DB_TRX_ID
, andDB_ROLL_PTR
).SPACE
An identifier for the tablespace where the table resides. 0 means the
InnoDB
system tablespace. Any other number represents a table created in file-per-table mode with a separate.ibd
file. This identifier stays the same after aTRUNCATE TABLE
statement. Other than the zero value, this identifier is unique for tables across all the databases in the instance.FILE_FORMAT
The table's file format (
Antelope
orBarracuda
).ROW_FORMAT
The table's row format (
Compact
,Redundant
,Dynamic
, orCompressed
).ZIP_PAGE_SIZE
The zip page size. Applies only to tables with a row format of
Compressed
.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE TABLE_ID = 74\G
*************************** 1. row ***************************
TABLE_ID: 74
NAME: test/t1
FLAG: 1
N_COLS: 6
SPACE: 60
FILE_FORMAT: Antelope
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
Notes
You must have the
PROCESS
privilege to query this table.Use the
INFORMATION_SCHEMA
COLUMNS
table or theSHOW COLUMNS
statement to view additional information about the columns of this table, including data types and default values.