21.30.22 The INFORMATION_SCHEMA INNODB_SYS

21.30.22 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table

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 the lower_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, and DB_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 a TRUNCATE 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 or Barracuda).
ROW_FORMAT
The table's row format (Compact, Redundant, Dynamic, or Compressed).
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 the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.