Information schema

In relational databases, the information schema (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. It can be used as a source of the information that some databases make available through non-standard commands, such as:


 * the  command of MySQL
 * the  command of Oracle's SQL*Plus
 * the  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
 * 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
 * Sybase ASE
 * Sybase SQL Anywhere
 * Teradata
 * Vertica