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.