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.
FLAG0 (InnoDB system table) or 1 (user table).
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

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