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.[1] It can be used as a source of the information that some databases make available through non-standard commands, such as:
- the
SHOWcommand of MySQL - the
DESCRIBEcommand of Oracle's SQL*Plus - the
\dcommand 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
[edit]As a notable exception among major database systems, Oracle does not as of 2015[update] 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
[edit]External links
[edit]- Information schema in H2 Database
- Information schema in MySQL 9.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
References
[edit]- ^
Melton, Jim; Simon, Alan R. (1993). "19.2 Metadata, Repositories and The INFORMATION_SCHEMA". Understanding the New SQL: A Complete Guide. The Morgan Kaufmann series in data management systems, ISSN 1046-1698. Morgan Kaufmann. p. 371. ISBN 9781558602458. Retrieved 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.
- ^ "Snowflake Information Schema". www.snowflake.com. Retrieved 2024-03-25.
- ^ "Pragma statements supported by SQLite". www.sqlite.org. Retrieved 2022-02-01.
 | This database-related article is a stub. You can help Wikipedia by expanding it. |