MySQL INFORMATION_SCHEMA

How to use MySQL’s INFORMATION_SCHEMA to query table metadata, find column definitions programmatically, audit database structure, debug schema issues, and replace SHOW commands with filterable SQL queries.

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

See also

• MySQL User Management — INFORMATION_SCHEMA.USER_PRIVILEGES for auditing grants
• Schema Migrations in MySQL — querying INFORMATION_SCHEMA to inspect schema before migrating