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. 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
Snowflake
MySQL
PostgreSQL
H2 Database
HSQLDB
InterSystems Caché
MariaDB
SingleStore (formerly MemSQL) Mimer SQL Trino Presto ClickHouse
RDBMSs that do not support information_schema include:
Apache Derby
Apache Ignite
Firebird
Microsoft Access
IBM Informix
Ingres
IBM Db2
MonetDB
Oracle Database
SAP HANA
SQLite
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