Skip to main content

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

TableWhat it contains
TABLESAll tables and views, with engine, row count estimates, sizes
COLUMNSAll columns with types, defaults, nullability
STATISTICSIndex definitions and cardinality
TABLE_CONSTRAINTSAll constraints (PK, UK, FK, CHECK)
KEY_COLUMN_USAGEColumns in constraints, with FK target details
REFERENTIAL_CONSTRAINTSFK cascade/restrict rules
CHECK_CONSTRAINTSCHECK constraint expressions
ROUTINESStored procedures and functions
TRIGGERSTrigger definitions
VIEWSView definitions
CHARACTER_SETSAvailable character sets
COLLATIONSAvailable collations
SCHEMATAAll databases visible to the current user

​
INFORMATION_SCHEMA vs SHOW Commands

SHOW commands are shorthand that often have an INFORMATION_SCHEMA equivalent:
SHOW commandINFORMATION_SCHEMA equivalent
SHOW TABLESSELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()
SHOW COLUMNS FROM tSELECT * FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 't'
SHOW INDEX FROM tSELECT * FROM information_schema.statistics WHERE table_name = 't'
SHOW CREATE TABLE tNo direct equivalent — SHOW CREATE TABLE is still the easiest way
SHOW PROCEDURE STATUSSELECT * 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

ProblemSolution
Query returns no rows for a known tableThe 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 nameConstraint names are scoped to the database — check constraint_schema in the WHERE clause
Slow query against INFORMATION_SCHEMA.TABLESInnoDB refreshes stats on access; use information_schema_stats_expiry (default 86400 seconds) to cache results longer

​
See also