Information schema: Difference between revisions
Content deleted Content added
mNo edit summary |
→Implementation: add Redshift |
||
| Line 47: | Line 47: | ||
RDBMSs that support information_schema include: |
RDBMSs that support information_schema include: |
||
* [[Amazon Redshift]] |
|||
* [[Apache Hive]] |
* [[Apache Hive]] |
||
* [[Microsoft SQL Server]] |
* [[Microsoft SQL Server]] |
||
Revision as of 15:50, 15 March 2023
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
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
- Snowflake
- MySQL
- PostgreSQL
- H2 Database
- HSQLDB
- InterSystems Caché
- MariaDB
- SingleStore (formerly MemSQL)
- Mimer SQL
- Trino
- Presto
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[2]
- Sybase ASE
- Sybase SQL Anywhere
- Teradata
- Vertica
See also
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
References
- ^
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.
- ^ "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. |