Information schema: Difference between revisions
Content deleted Content added
→Implementation: add Redshift |
|||
| (10 intermediate revisions by 7 users not shown) | |||
| Line 50: | Line 50: | ||
* [[Apache Hive]] |
* [[Apache Hive]] |
||
* [[Microsoft SQL Server]] |
* [[Microsoft SQL Server]] |
||
| ⚫ | |||
* [[Snowflake Inc.|Snowflake]] |
* [[Snowflake Inc.|Snowflake]] |
||
* [[MySQL]] |
* [[MySQL]] |
||
| Line 59: | Line 60: | ||
* [[SingleStore]] (formerly MemSQL) |
* [[SingleStore]] (formerly MemSQL) |
||
* [[Mimer SQL]] |
* [[Mimer SQL]] |
||
* [[Snowflake]]<ref>{{Cite web|title=Snowflake Information Schema|url=https://docs.snowflake.com/en/sql-reference/info-schema|access-date=2024-03-25|website=www.snowflake.com}}</ref> |
|||
* [[Trino_(SQL_query_engine)|Trino]] |
* [[Trino_(SQL_query_engine)|Trino]] |
||
* [[Presto_(SQL_query_engine)|Presto]] |
* [[Presto_(SQL_query_engine)|Presto]] |
||
* [[CrateDB]] |
|||
* [[ClickHouse]] |
|||
* [[CockroachDB]] |
|||
RDBMSs that do not support information_schema include: |
RDBMSs that do not support information_schema include: |
||
| Line 71: | Line 76: | ||
* [[Ingres_(database)|Ingres]] |
* [[Ingres_(database)|Ingres]] |
||
* [[IBM Db2]] |
* [[IBM Db2]] |
||
| ⚫ | |||
* [[Oracle Database]] |
* [[Oracle Database]] |
||
* [[SAP HANA]] |
* [[SAP HANA]] |
||
| Line 85: | Line 89: | ||
== External links == |
== External links == |
||
* [http://h2database.com/html/grammar.html#information_schema Information schema in H2 Database] |
* [http://h2database.com/html/grammar.html#information_schema Information schema in H2 Database] |
||
* [http://dev.mysql.com/doc/refman/ |
* [http://dev.mysql.com/doc/refman/9.0/en/information-schema.html Information schema in MySQL 9.0] |
||
* [http://www.postgresql.org/docs/current/interactive/information-schema.html Information schema in PostgreSQL (current version)] |
* [http://www.postgresql.org/docs/current/interactive/information-schema.html Information schema in PostgreSQL (current version)] |
||
* [https://www.sqlite.org/pragma.html#pragfunc Information schema in SQLite] |
* [https://www.sqlite.org/pragma.html#pragfunc Information schema in SQLite] |
||
| Line 92: | Line 96: | ||
* [http://sourceforge.net/projects/ora-info-schema/ Oracle Information Schema project on sourceforge] |
* [http://sourceforge.net/projects/ora-info-schema/ Oracle Information Schema project on sourceforge] |
||
* [https://mariadb.com/kb/en/mariadb/information-schema/ Information Schema in MariaDB] |
* [https://mariadb.com/kb/en/mariadb/information-schema/ Information Schema in MariaDB] |
||
* [https://www.monetdb.org/documentation/user-guide/sql-catalog/information_schema/ Information Schema in MonetDB] |
|||
* [https://cratedb.com/docs/crate/reference/en/latest/general/information-schema.html Information Schema in CrateDB] |
|||
* [https://www.cockroachlabs.com/docs/stable/information-schema Information Schema in CockroachDB] |
|||
== References == |
== References == |
||
Latest revision as of 11:05, 16 September 2024
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. |