Home›SQL›MySQL – INFORMATION_SCHEMA

MySQL – INFORMATION_SCHEMA

December 7, 20244 min readUpdated 8/25/2026

INFORMATION_SCHEMA is the database describing itself, as ordinary tables you can query. That is the useful part: anything you can ask about your data, you can ask about your schema — join it, filter it, aggregate it, generate SQL from it.

SHOW commands read the same underlying data in a friendlier form. SHOW for a quick look; INFORMATION_SCHEMA when you need to query the answer.

What tables exist

SELECT TABLE_NAME, ENGINE, TABLE_COLLATION
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'pizza' AND TABLE_TYPE = 'BASE TABLE'
ORDER  BY TABLE_NAME LIMIT 5;
+-----------------------+--------+--------------------+
| TABLE_NAME            | ENGINE | TABLE_COLLATION    |
+-----------------------+--------+--------------------+
| DATABASECHANGELOG     | InnoDB | utf8mb4_0900_ai_ci |
| DATABASECHANGELOGLOCK | InnoDB | utf8mb4_0900_ai_ci |
| app_user              | InnoDB | utf8mb4_0900_ai_ci |
| cart                  | InnoDB | utf8mb4_0900_ai_ci |
| cart_item             | InnoDB | utf8mb4_0900_ai_ci |
+-----------------------+--------+--------------------+

TABLE_SCHEMA is the database name — MySQL uses "schema" and "database" interchangeably, unlike Postgres. TABLE_TYPE separates tables from views. This is also a fast audit: an ENGINE that is not InnoDB or a collation that is not utf8mb4 is worth knowing about.

Columns

SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY
FROM   information_schema.COLUMNS
WHERE  TABLE_SCHEMA = 'pizza' AND TABLE_NAME = 'crust'
ORDER  BY ORDINAL_POSITION;
+---------------+---------------+-------------+------------+
| COLUMN_NAME   | COLUMN_TYPE   | IS_NULLABLE | COLUMN_KEY |
+---------------+---------------+-------------+------------+
| id            | bigint        | NO          | PRI        |
| name          | varchar(80)   | NO          | UNI        |
| price_delta   | decimal(10,2) | NO          |            |
| active        | tinyint(1)    | NO          |            |
| display_order | int           | NO          |            |
| public_id     | char(36)      | NO          | UNI        |
| created_at    | datetime(6)   | NO          |            |
| updated_at    | datetime(6)   | NO          |            |
| deleted       | tinyint(1)    | NO          | MUL        |
+---------------+---------------+-------------+------------+

Because it is a table, you can ask questions DESCRIBE cannot — every column called email across the whole database, every DECIMAL that is not (10,2), every nullable column with no default:

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE
FROM   information_schema.COLUMNS
WHERE  TABLE_SCHEMA = 'pizza' AND DATA_TYPE = 'decimal' AND COLUMN_TYPE <> 'decimal(10,2)';

What points at this table

The question to answer before deleting anything, because of ON DELETE CASCADE:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME
FROM   information_schema.KEY_COLUMN_USAGE
WHERE  TABLE_SCHEMA = 'pizza' AND REFERENCED_TABLE_NAME = 'customer_order'
ORDER  BY TABLE_NAME;
+------------+-------------+---------------------+-----------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME     | REFERENCED_TABLE_NAME |
+------------+-------------+---------------------+-----------------------+
| order_item | order_id    | fk_order_item_order | customer_order        |
+------------+-------------+---------------------+-----------------------+

KEY_COLUMN_USAGE gives the columns; REFERENTIAL_CONSTRAINTS adds the DELETE_RULE and UPDATE_RULE, which is what tells you whether a delete cascades:

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME, DELETE_RULE
FROM   information_schema.REFERENTIAL_CONSTRAINTS
WHERE  CONSTRAINT_SCHEMA = 'pizza' AND DELETE_RULE = 'CASCADE'
ORDER  BY TABLE_NAME;

Indexes

SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns_, NON_UNIQUE
FROM   information_schema.STATISTICS
WHERE  TABLE_SCHEMA = 'pizza' AND TABLE_NAME = 'customer_order'
GROUP  BY INDEX_NAME, NON_UNIQUE ORDER BY INDEX_NAME;
+-----------------------------------+--------------------------+------------+
| INDEX_NAME                        | columns_                 | NON_UNIQUE |
+-----------------------------------+--------------------------+------------+
| idx_customer_order_created_at     | created_at               |          1 |
| idx_customer_order_deleted        | deleted                  |          1 |
| idx_customer_order_payment_intent | stripe_payment_intent_id |          1 |
| idx_customer_order_status         | status                   |          1 |
| idx_customer_order_user           | user_id                  |          1 |
| PRIMARY                           | id                       |          0 |
| uk_customer_order_public_id       | public_id                |          0 |
+-----------------------------------+--------------------------+------------+

STATISTICS has one row per column per index, so the GROUP_CONCAT with SEQ_IN_INDEX is what reassembles a composite index into something readable. NON_UNIQUE = 0 means unique.

Two audits worth running on any schema you inherit. Redundant indexes — one whose columns are a leading prefix of another's, which the larger index already serves (see indexes). And unused indexes, which INFORMATION_SCHEMA cannot tell you, because it knows the schema and not the workload — that lives in sys.schema_unused_indexes, built on performance_schema.

Size on disk

SELECT TABLE_NAME,
       ROUND(DATA_LENGTH  / 1024 / 1024, 1) AS data_mb,
       ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS index_mb,
       TABLE_ROWS AS approx_rows
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'pizza_lab'
ORDER  BY DATA_LENGTH + INDEX_LENGTH DESC;

⚠️ TABLE_ROWS is an estimate, not a count. For InnoDB it is sampled from the index and can be out by a wide margin — the same sampling that makes EXPLAIN's rows drift. Use it for "roughly how big", never for a number anyone will act on. COUNT(*) is the count.

DATA_LENGTH and INDEX_LENGTH are approximate for the same reason, and they do not shrink when you delete rows — InnoDB keeps the space for reuse.

Generating SQL from it

The trick that makes this genuinely powerful: query the catalogue to write your DDL.

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 ',
              'COLLATE utf8mb4_0900_ai_ci;') AS statement
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'pizza' AND TABLE_COLLATION <> 'utf8mb4_0900_ai_ci';

Run it, read every line of the output, then paste the lines you agree with. Read them. A generated statement is only as good as the WHERE clause that produced it, and this technique makes it equally easy to generate 200 correct statements or 200 wrong ones.

The neighbouring schemas

information_schemaWhat the schema is. Standard SQL, portable in spirit.
performance_schemaWhat the server is doing — statement latency, waits, locks. Detailed and verbose.
sysReadable views over performance_schema. sys.schema_unused_indexes, sys.statements_with_full_table_scans, sys.schema_table_lock_waits — start here.
mysqlThe server's own tables, including accounts and grants. Read it; do not write it.

What to remember

  • It is queryable, so ask questions SHOW cannot answer.
  • KEY_COLUMN_USAGE and REFERENTIAL_CONSTRAINTS before you drop or delete.
  • STATISTICS needs GROUP_CONCAT ... SEQ_IN_INDEX to read composites.
  • TABLE_ROWS is an estimate. COUNT(*) is the count.
  • Generate DDL from it — and read every generated line before running it.