MySQL INFORMATION_SCHEMA

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

INFORMATION_SCHEMA is a virtual database that exposes MySQL server metadata through standard SQL queries. Everything that SHOW TABLES, SHOW COLUMNS, and DESCRIBE can tell you, INFORMATION_SCHEMA can too — and with the full power of WHERE, JOIN, and aggregation.

Tables and Columns

List all tables in a database:

SELECT table_name, table_type, engine, table_rows, create_time
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY table_name;

table_rows is an estimate for InnoDB tables (derived from index statistics), not an exact count. Use SELECT COUNT(*) for precision. List all columns for a table:

SELECT column_name, column_type, is_nullable, column_default, extra
FROM information_schema.columns
WHERE table_schema = 'myapp'
  AND table_name = 'users'
ORDER BY ordinal_position;

Find all columns of a specific type across all tables:

SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'myapp'
  AND data_type = 'json';

Indexes

List all indexes on a table:

SELECT index_name, non_unique, seq_in_index, column_name, index_type
FROM information_schema.statistics
WHERE table_schema = 'myapp'
  AND table_name = 'orders'
ORDER BY index_name, seq_in_index;

Find tables with no primary key:

SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_schema = 'myapp'
  AND t.table_type = 'BASE TABLE'
  AND NOT EXISTS (
    SELECT 1
    FROM information_schema.table_constraints tc
    WHERE tc.table_schema = t.table_schema
      AND tc.table_name = t.table_name
      AND tc.constraint_type = 'PRIMARY KEY'
  );

Constraints

List all constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK):

SELECT constraint_name, constraint_type, table_name
FROM information_schema.table_constraints
WHERE table_schema = 'myapp'
ORDER BY table_name, constraint_type;

List all foreign keys and what they reference:

SELECT
    kcu.table_name,
    kcu.column_name,
    kcu.constraint_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name,
    rc.update_rule,
    rc.delete_rule
FROM information_schema.key_column_usage kcu
JOIN information_schema.referential_constraints rc
    ON rc.constraint_schema = kcu.constraint_schema
    AND rc.constraint_name = kcu.constraint_name
WHERE kcu.table_schema = 'myapp'
  AND kcu.referenced_table_name IS NOT NULL
ORDER BY kcu.table_name;

List CHECK constraints:

SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'myapp'
ORDER BY constraint_name;

Stored Routines

List all stored procedures and functions:

SELECT routine_name, routine_type, created, last_altered
FROM information_schema.routines
WHERE routine_schema = 'myapp'
ORDER BY routine_type, routine_name;

View a routine’s definition:

SELECT routine_definition
FROM information_schema.routines
WHERE routine_schema = 'myapp'
  AND routine_name = 'get_active_users'
  AND routine_type = 'PROCEDURE'\G

Views and Triggers

List all views:

SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'myapp'\G

List all triggers:

SELECT trigger_name, event_manipulation, event_object_table,
       action_timing, action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'myapp'
ORDER BY event_object_table, action_timing;

Privileges

View effective privileges for the current user:

SELECT * FROM information_schema.user_privileges;
SELECT * FROM information_schema.schema_privileges;
SELECT * FROM information_schema.table_privileges;

Key Tables Reference

Table	What it contains
`TABLES`	All tables and views, with engine, row count estimates, sizes
`COLUMNS`	All columns with types, defaults, nullability
`STATISTICS`	Index definitions and cardinality
`TABLE_CONSTRAINTS`	All constraints (PK, UK, FK, CHECK)
`KEY_COLUMN_USAGE`	Columns in constraints, with FK target details
`REFERENTIAL_CONSTRAINTS`	FK cascade/restrict rules
`CHECK_CONSTRAINTS`	CHECK constraint expressions
`ROUTINES`	Stored procedures and functions
`TRIGGERS`	Trigger definitions
`VIEWS`	View definitions
`CHARACTER_SETS`	Available character sets
`COLLATIONS`	Available collations
`SCHEMATA`	All databases visible to the current user

INFORMATION_SCHEMA vs SHOW Commands

SHOW commands are shorthand that often have an INFORMATION_SCHEMA equivalent:

SHOW command	INFORMATION_SCHEMA equivalent
`SHOW TABLES`	`SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()`
`SHOW COLUMNS FROM t`	`SELECT * FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 't'`
`SHOW INDEX FROM t`	`SELECT * FROM information_schema.statistics WHERE table_name = 't'`
`SHOW CREATE TABLE t`	No direct equivalent — `SHOW CREATE TABLE` is still the easiest way
`SHOW PROCEDURE STATUS`	`SELECT * FROM information_schema.routines WHERE routine_type = 'PROCEDURE'`

INFORMATION_SCHEMA queries are better when you need filtering, joining, or aggregation. SHOW commands are fine for interactive inspection.

Frequently Asked Questions

Are INFORMATION_SCHEMA queries slow?

For InnoDB tables, some queries (especially against TABLES and STATISTICS) trigger index statistics updates, which can be slow on large schemas. If you’re querying metadata frequently, use mysql.innodb_table_stats and mysql.innodb_index_stats for faster access to cached statistics.

Why do table_rows values look wrong?

information_schema.tables.table_rows is an estimate from InnoDB’s index statistics, not a live count. It can be off by 40-50% for large tables. Run ANALYZE TABLE tablename to refresh the statistics, or use SELECT COUNT(*) for an exact count.

Troubleshooting

Problem	Solution
Query returns no rows for a known table	The current user may lack `SELECT` privilege on the table — `INFORMATION_SCHEMA` filters results to tables the user can see
`table_rows` is very different from COUNT(*)	InnoDB row counts are estimates — run `ANALYZE TABLE` to refresh index stats
Can’t find a constraint by name	Constraint names are scoped to the database — check `constraint_schema` in the WHERE clause
Slow query against `INFORMATION_SCHEMA.TABLES`	InnoDB refreshes stats on access; use `information_schema_stats_expiry` (default 86400 seconds) to cache results longer

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

Tables and Columns

Indexes

Constraints

Stored Routines

Views and Triggers

Privileges

Key Tables Reference

INFORMATION_SCHEMA vs SHOW Commands

Frequently Asked Questions

Are INFORMATION_SCHEMA queries slow?

Why do table_rows values look wrong?

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​Tables and Columns

​Indexes

​Constraints

​Stored Routines

​Views and Triggers

​Privileges

​Key Tables Reference

​INFORMATION_SCHEMA vs SHOW Commands

​Frequently Asked Questions

​Are INFORMATION_SCHEMA queries slow?

​Why do table_rows values look wrong?

​Troubleshooting

​See also

Tables and Columns

Indexes

Constraints

Stored Routines

Views and Triggers

Privileges

Key Tables Reference

INFORMATION_SCHEMA vs SHOW Commands

Frequently Asked Questions

Are INFORMATION_SCHEMA queries slow?

Why do table_rows values look wrong?

Troubleshooting

See also