Piotr Kononow
INFORMATION_SCHEMA is schema with a set of standard views/tables (depending on specific database engine) providing access to the database metadata and data dictionary - list tables, views, columns, stored procedures or functions in a database. This standard is part of SQL-92 and was implemented by some of the popular databases.
Standard views
SQL-92 specifies a set of standard tables/views that each database should support:
Some of the standard views:
- TABLES - lists tables in a database
- VIEWS - lists views in database
- COLUMNS - lists table and view columns
- TABLE_CONSTRAINTS - lists table constraints: unique, primary, foreign key and check constraints.
- REFERENTIAL_CONSTRAINTS - lists foreign keys
Full list includes over 20 views.
Deviations from standard and issues
The standard was widely adopted by database vendors, yet no one impmenation was fully compliant with it. Vendors treated it more as a guidelines rather that fixed standard.
There are a number of deviations and issues with information_schema views in different databases.
Ommited views
Some views are simply ingored and not supported. Example of such case is VIEW_COLUMN_USAGE in MySQL.
Missing data
Quite often, views and columns are in place but data is missing or not reliable.
Added views and columns
Because databases have their specific elements vendors add their own columns. For instance, MySQL has a ROUTINES view that holds stored procedures and user defined functions.
Alternative catalogs
Because infortmation_schema stadard is limiting and doesn't reflect unique features of databases vendors created their own system catalogs (data dictionaries). SQL Server has sys schema, Oracle ALL_, USER_, and DBA_ views and PostgreSQL has pg_ views.
See the list of database system catalogs
What databases support INFORMATION_SCHEMA views
Below is a summary of information_schema support by various SQL databases. In Alternative catalog column you will find database specific catalogs, that in most cases are recommended over information_schema.
| Database | Information schema | Alternative catalog |
|---|---|---|
| SQL Server | Yes | System Catalog Views (sys schema) |
| Azure SQL Database | Yes | System Catalog Views (sys schema) |
| Oracle database | No | Data Dictionary Views / Catalog Views |
| MySQL | Yes | No |
| PostgreSQL | Yes | System Catalogs (pg_) |
| MariaDB | Yes | No |
| IBM Db2 | No | Catalog views (SYSCAT schema) |
| Microsoft Access | No | Database obejct listing (MSysObjects) |
| Amazon Redshift | Yes | System Catalog Tables (pg_) |
| Snowflake | Yes | No |
| Teradata | No | Data Dictionary Views (DBC schema) |
| SAP/Sybase ASE | No | System Tables (sys) |
| SAP HANA | No | System Views (sys schema) |
| Vertica | No | System Tables (v_catalog schema) |
| IBM Informix | Yes | System Catalog Table ("informix".sys) |
| SQLite | No | System Table (sqlite_master) |
| Firebird | No | System Tables (names begin with RDB$) |
| Interbase | No | System Tables (names begin with RDB$) |
Information_schema / system catalog queries
We are proud that we launched and commit to updating grand repository of queries to system catalogs of various popular databases.
Visit Database Query Toolbox - probably the biggest repository of queries (after StackOverflow) on the web.