Information schema: Difference between revisions
Content deleted Content added
m Grammar |
|||
| Line 1: | Line 1: | ||
In [[relational database]]s, the '''information schema''' (<tt>information_schema</tt>) is an [[ANSI]]-standard set of read-only views |
In [[relational database]]s, the '''information schema''' (<tt>information_schema</tt>) is an [[ANSI]]-standard set of read-only views that provide information about all of the [[table (database)|table]]s, [[view (database)|view]]s, [[column (database)|column]]s, and [[Stored procedure|procedures]] in a database.<ref> |
||
{{cite book |
{{cite book |
||
| last1 = Melton |
| last1 = Melton |
||
| Line 18: | Line 18: | ||
| quote = 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. |
| quote = 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. |
||
}} |
}} |
||
| ⚫ | |||
</ref> |
|||
| ⚫ | |||
* the <code>SHOW</code> command of [[MySQL]] |
* the <code>SHOW</code> command of [[MySQL]] |
||
| Line 46: | Line 45: | ||
As a notable exception among major database systems, Oracle does not {{as of | 2015 | lc = on}} implement the information schema. An [http://sourceforge.net/projects/ora-info-schema/ open-source project] exists to address this. |
As a notable exception among major database systems, Oracle does not {{as of | 2015 | lc = on}} implement the information schema. An [http://sourceforge.net/projects/ora-info-schema/ open-source project] exists to address this. |
||
RDBMSs |
RDBMSs that support information_schema include: |
||
* [[Apache Hive]] |
* [[Apache Hive]] |
||
| Line 59: | Line 58: | ||
* [[MemSQL]] |
* [[MemSQL]] |
||
RDBMSs |
RDBMSs that do not support information_schema include: |
||
* [[Apache Derby]] |
* [[Apache Derby]] |
||
Revision as of 21:55, 30 May 2020
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:
- Apache Hive
- Microsoft SQL Server
- MySQL
- PostgreSQL
- H2 Database
- HSQLDB
- InterSystems Caché
- MariaDB
- Presto
- MemSQL
RDBMSs that do not support information_schema include:
- Apache Derby
- Firebird
- Microsoft Access
- IBM Informix
- Ingres
- IBM DB2
- Oracle Database
- SAP HANA
- SQLite
- 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.
 | This database-related article is a stub. You can help Wikipedia by expanding it. |