Information Schema INNODB_SYS_TABLES Table

The Information Schema INNODB_SYS_TABLES table contains information about InnoDB tables.

The PROCESS privilege is required to view the table.

It has the following columns:

ColumnDescriptionAdded
TABLE_IDUnique InnoDB table identifier.
NAMEDatabase and table name, or the uppercase InnoDB system table name.
FLAGSee Flag below.
N_COLSNumber of columns in the table.
SPACETablespace identifier where the index resides. 0 represents the InnoDB system tablespace, while any other value represents a table created in file-per-table mode (see the innodb_file_per_table system variable). Remains unchanged after a TRUNCATE TABLE statement.
FILE_FORMATInnoDB file format (Antelope or Barracuda).MariaDB 10.0
ROW_FORMATInnoDB storage format (Compact, Redundant, Dynamic, or Compressed).MariaDB 10.0
ZIP_PAGE_SIZEFor Compressed tables, the zipped page size.MariaDB 10.0

Flag

The flag field returns the dict_table_t::flags that correspond to the data dictionary record.

BitDescription
0Set if ROW_FORMAT is not REDUNDANT.
1 to 40, except for ROW_FORMAT=COMPRESSED, where they will determine the KEY_BLOCK_SIZE (the compressed page size).
5Set for ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED.
6Set if the DATA DIRECTORY attribute was present when the table was originally created.
7Set if the page_compressed attribute is present.
8 to 11Determine the page_compression_level.
12 13Normally 00, but 11 for "no-rollback tables" (MariaDB 10.3 CREATE SEQUENCE). In MariaDB 10.1, these bits could be 01 or 10 for ATOMIC_WRITES=ON or ATOMIC_WRITES=OFF.

Note that the table flags returned here are not the same as tablespace flags (FSP_SPACE_FLAGS).

Example

SELECT * FROM information_schema.INNODB_SYS_TABLES LIMIT 2\G
*************************** 1. row ***************************
     TABLE_ID: 14
         NAME: SYS_DATAFILES
         FLAG: 0
       N_COLS: 5
        SPACE: 0
  FILE_FORMAT: Antelope
   ROW_FORMAT: Redundant
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: System
*************************** 2. row ***************************
     TABLE_ID: 11
         NAME: SYS_FOREIGN
         FLAG: 0
       N_COLS: 7
        SPACE: 0
  FILE_FORMAT: Antelope
   ROW_FORMAT: Redundant
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: System
2 rows in set (0.00 sec)

See Also

Comments

Comments loading...