Information schema
From Wikipedia, the free encyclopedia
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
- Kinetica DB
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日.
