> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # 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. 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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): ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} SELECT table_name, view_definition FROM information_schema.views WHERE table_schema = 'myapp'\G ``` List all triggers: ```sql theme={null} 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: ```sql theme={null} 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](/guides/user-management) — INFORMATION\_SCHEMA.USER\_PRIVILEGES for auditing grants * [Schema Migrations in MySQL](/guides/schema-migrations) — querying INFORMATION\_SCHEMA to inspect schema before migrating