21.29.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”.

Table 21.22 INNODB_SYS_TABLES Columns

Column name	Description
TABLE_ID	An identifier for each InnoDB table that is unique across all databases in the instance.
NAME	The name of the table. Preceded by the database name where appropriate, for example test/t1. InnoDB system table names are in all uppercase. 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	This value provides bit level information about table format and storage characteristics including row format, compressed page size (if applicable), and whether or not the DATA DIRECTORY clause was used with CREATE TABLE or ALTER TABLE.
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. Only applies to tables that use the Compressed row format.

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:

• Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

• You must have the PROCESS privilege to query this table.

Interpreting the INNODB_SYS_TABLES.FLAG Column Value:

The INNODB_SYS_TABLES.FLAG column provides bit-level information about the table's format and storage characteristics. You can interpret the FLAG column value by adding together the applicable decimal numeric values that are provided in the following table.

Table 21.23 Bit Position Values for Interpreting INNODB_SYS_TABLES FLAG Column Data

Bit Position	Description	Decimal Numeric Value
0	This bit is set if the row format is not REDUNDANT. In other words, it is set if the row format is COMPACT, DYNAMIC or COMPRESSED.	0 - REDUNDANT 1 - COMPACT, DYNAMIC or COMPRESSED
1-4	These four bits contain a small number that represents the compressed page size of the table. The INNODB_SYS_TABLES.ZIP_PAGE_SIZE field also reports the compressed page size, if applicable.	0 - Not Compressed 2 - 1024 Byte Compressed Page Size 4 - 2048 Byte Compressed Page Size 6 - 4096 Byte Compressed Page Size 8 - 8192 Byte Compressed Page Size 10 - 16384 Byte Compressed Page Size
5	This bit is set if the row format is DYNAMIC or COMPRESSED.	0 - REDUNDANT or COMPACT 32 - DYNAMIC or COMPRESSED
6	This bit is set if the DATA DIRECTORY option is used with CREATE TABLE or ALTER TABLE. This bit is set for file-per-table tablespaces that are located in directories other than the default data directory (datadir). For these tables, a tablename.isl file is present in the same location as the tablename.frm file. The tablename.isl file stores the actual directory path to the tablename.ibd file-per-table tablespace file.	0 - Not a remote file-per-table tablespace 64 - A remote file-per-table tablespace

In the following, table t1 uses ROW_FORMAT=DYNAMIC and has a FLAG value of 33. Based on the information in the preceding table, we can see that bit position 0 would be set to 1, and bit position 5 would be set to 32 for a table with a DYNAMIC row format. These values add up to a FLAG value of 33.

mysql> use test;
Database changed

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (c1 int) ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'\G
*************************** 1. row ***************************
     TABLE_ID: 89
         NAME: test/t1
         FLAG: 33
       N_COLS: 4
        SPACE: 75
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
1 row in set (0.00 sec)