The INFORMATION_SCHEMA or equivalent system catalogs for introspection.
A standard SQL schema providing read-only access to database metadata: tables, columns, constraints, privileges. Portable across databases (with variations). Preferred over querying system catalog tables directly for application portability.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | 5.0 | MySQL has supported INFORMATION_SCHEMA since 5.0. |
| PostgreSQL | Native | 9.1 | PostgreSQL uses system catalogs like pg_tables and information_schema for metadata. |
| SQL Server | Native | 2000 | SQL Server has supported INFORMATION_SCHEMA since SQL Server 2000, alongside sys.tables and other catalog views. |
| Oracle | Native | 12c | Oracle uses system catalogs like ALL_TABLES for metadata introspection. |
| SQLite | Not Supported | - | SQLite exposes metadata via the sqlite_master table and PRAGMA interfaces rather than a standard INFORMATION_SCHEMA. |
Details
Understanding the available tables and columns in a database is crucial for building tools that rely on metadata.
Standard Syntax
Version Support
MySQL: Native since 5.0
PostgreSQL: Native since 9.1
SQL Server: Native since 2000
Oracle: Native since 12c
SQLite: Not supported
Per-Database Syntax & Notes
SQLite Alternative syntax
SQLite exposes metadata via the sqlite_master table and PRAGMA interfaces rather than a standard INFORMATION_SCHEMA.
SELECT * FROM sqlite_master; PRAGMA table_info(table_name);