INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.
These are the Open Table Format Catalogs of today’s world, where you query what tables are in a relational database.
Similar to the Hive Metastore before, an index for what tables you have in your Data Lake.
# Queries for Database Exploration
Here are some of the most commonly used queries.
12345
| -- List all tables in the databaseSELECT table_schema, table_name, table_typeFROM INFORMATION_SCHEMA.TABLESWHERE table_schema NOT IN ('information_schema', 'pg_catalog', 'sys')ORDER BY table_schema, table_name;
|
123456
| -- Get column details for a specific tableSELECT column_name, data_type, character_maximum_length, is_nullable, column_defaultFROM INFORMATION_SCHEMA.COLUMNSWHERE table_schema = 'your_schema' AND table_name = 'your_table'ORDER BY ordinal_position;
|
# Primary Keys
1 2 3 4 5 6 7 8 910
| -- Find primary keysSELECT tc.table_schema, tc.table_name, kc.column_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcJOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc ON tc.constraint_name = kc.constraint_nameWHERE tc.constraint_type = 'PRIMARY KEY'ORDER BY tc.table_schema, tc.table_name;
|
# Foreign Keys
Foreign Key exploration:
1 2 3 4 5 6 7 8 91011121314
| -- Find foreign key relationshipsSELECT fk.table_schema, fk.table_name, fk.column_name, fk.constraint_name, rc.referenced_table_schema, rc.referenced_table_name, rc.referenced_column_nameFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkJOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON fk.constraint_name = rc.constraint_nameWHERE rc.referenced_table_name IS NOT NULLORDER BY fk.table_schema, fk.table_name;
|
# Indexes
1 2 3 4 5 6 7 8 9101112
| -- List indexes (implementation varies by database system)-- For PostgreSQL:SELECT t.relname AS table_name, i.relname AS index_name, a.attname AS column_nameFROM pg_class tJOIN pg_index ix ON t.oid = ix.indrelidJOIN pg_class i ON i.oid = ix.indexrelidJOIN pg_attribute a ON a.attnum = ANY(ix.indkey) AND a.attrelid = t.oidWHERE t.relkind = 'r'ORDER BY t.relname, i.relname;
|
# Database Size and Growth
123456
| -- Database size (PostgreSQL example)SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS sizeFROM pg_databaseORDER BY pg_database_size(pg_database.datname) DESC;
|
# Table Size
1234567
| -- Table sizes (PostgreSQL example)SELECT table_name, pg_size_pretty(pg_total_relation_size(table_name)) AS total_sizeFROM information_schema.tablesWHERE table_schema = 'your_schema'ORDER BY pg_total_relation_size(table_name) DESC;
|
# Supported Databases
Full Support:
MySQL/MariaDB: Extensive implementation of INFORMATION_SCHEMAMicrosoft SQL Server: Comprehensive support with Microsoft extensionsPostgreSQL: Supports INFORMATION_SCHEMA plus additional system catalogsOracle Database: Has INFORMATION_SCHEMA views (but traditionally relies more on its own data dictionary views like ALL_TABLES, DBA_TABLES)IBM Db2: Supports INFORMATION_SCHEMA
Partial/Alternative Implementations:
SQLite: Does not have INFORMATION_SCHEMA but provides similar functionality through SQLite system tables and PRAGMA statementsDuckDB: Has INFORMATION_SCHEMAH2 Database: Supports INFORMATION_SCHEMASnowflake: Supports INFORMATION_SCHEMA with cloud data warehouse adaptationsAmazon Redshift: Provides INFORMATION_SCHEMA viewsGoogle BigQuery: Has INFORMATION_SCHEMA with cloud-specific adaptations
Origin: Open Table Format Catalogs
References:
Created 2025-04-30
