Information schema explained

In relational databases, the information schema is an ANSI-standard set of read-only views that provide information about all of the tables, views, columns, and procedures in a database.^[1] It can be used as a source of the information that some databases make available through non-standard commands, such as:

• the SHOW command of MySQL
• the DESCRIBE command of Oracle's SQL*Plus
• the \d command in psql (PostgreSQL's default command-line program).

=> SELECT count(table_name) FROM information_schema.tables; count ------- 99 (1 row) => SELECT column_name, data_type, column_default, is_nullable FROM information_schema.columns WHERE table_name='alpha'; column_name | data_type | column_default | is_nullable -------------+-----------+----------------+------------- foo | integer | | YES bar | character | | YES (2 rows) => SELECT * FROM information_schema.information_schema_catalog_name; catalog_name -------------- johnd (1 row)

Implementation

As a notable exception among major database systems, Oracle does not implement the information schema. An open-source project exists to address this.

RDBMSs that support information_schema include:

• Amazon Redshift
• Apache Hive
• Microsoft SQL Server
• MonetDB
• Snowflake
• MySQL
• PostgreSQL
• H2 Database
• HSQLDB
• InterSystems Caché
• MariaDB
• SingleStore (formerly MemSQL)
• Mimer SQL
• Snowflake^[2]
• Trino
• Presto
• CrateDB
• ClickHouse
• CockroachDB

RDBMSs that do not support information_schema include:

• Apache Derby
• Apache Ignite
• Firebird
• Microsoft Access
• IBM Informix
• Ingres
• IBM Db2
• Oracle Database
• SAP HANA
• SQLite^[3]
• Sybase ASE
• Sybase SQL Anywhere
• Teradata
• Vertica

See also

• Oracle metadata

External links

• Information schema in H2 Database
• Information schema in MySQL 8.0
• Information schema in PostgreSQL (current version)
• Information schema in SQLite
• Information schema in Microsoft SQL Server 2014
• Information schema in Microsoft SQL Server Compact 4.0
• Oracle Information Schema project on sourceforge
• Information Schema in MariaDB
• Information Schema in MonetDB
• Information Schema in CrateDB
• Information Schema in CockroachDB

Notes and References

1. Book: Melton. Jim. Simon. Alan R.. 19.2 Metadata, Repositories and The INFORMATION_SCHEMA. Understanding the New SQL: A Complete Guide. 1993. registration. The Morgan Kaufmann series in data management systems, ISSN 1046-1698. Morgan Kaufmann. 1993. 371. 9781558602458. 2015-10-22. Metadata that applies primarily to the runtime database environment is managed through the INFORMATION_SCHEMA. [...] Metadata that applies to the information system environment as a whole is managed through the dictionary or repository..
2. Web site: Snowflake Information Schema. 2024-03-25. www.snowflake.com.
3. Web site: Pragma statements supported by SQLite. 2022-02-01. www.sqlite.org.