Information schema: Difference between revisions

Content deleted Content added

Inline

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 SHOW command of MySQL
the DESCRIBE command of Oracle's SQL*Plus
the \d command 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:

RDBMSs that do not support information_schema include:

External links

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.
^ "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.

[1] 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.

[2] "Snowflake Information Schema". www.snowflake.com. Retrieved 2024-03-25.

[3] "Pragma statements supported by SQLite". www.sqlite.org. Retrieved 2022-02-01.

[1]

[2]

[3]

@@ Line 1: / Line 1: @@
-In [[relational database]]s, the '''information schema''' is an [[ANSI]] standard set of read-only views which 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.  It can be used as a source of the information which some databases make available through non-standard commands, such as the <code>SHOW</code> command of [[MySQL]], the <code>DESCRIBE</code> command of [[Oracle database|Oracle]], and the <code>\d</code> command in [[PostgreSQL#psql|psql]] ([[PostgreSQL]]'s default terminal).
+In [[relational database]]s, the '''information schema''' ({{mono|information_schema}}) 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
+| last1                 = Melton
+| first1                = Jim
+| last2                 = Simon
+| first2                = Alan R.
+| chapter               = 19.2 Metadata, Repositories and The INFORMATION_SCHEMA
+| title                 = Understanding the New SQL: A Complete Guide
+| year                 = 1993
+| url                   = https://archive.org/details/understandingnew00melt
+| url-access            = registration
+| series                = The Morgan Kaufmann series in data management systems, ISSN 1046-1698
+| publisher             = Morgan Kaufmann
+| publication-date      = 1993
+| page                  = [https://archive.org/details/understandingnew00melt/page/371 371]
+| isbn                  = 9781558602458
+| accessdate            = 2015-10-22
+| 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> It can be used as a source of the information that some databases make available through non-standard commands, such as:
+* the <code>SHOW</code> command of [[MySQL]]
-<code>
+* the <code>DESCRIBE</code> command of [[Oracle database|Oracle]]'s [[SQL*Plus]]
-  => select count(table_name) from information_schema.tables;
+* the <code>\d</code> command in [[PostgreSQL#psql|psql]] ([[PostgreSQL]]'s default command-line program).
+  => SELECT count(table_name) FROM information_schema.tables;
    count
   -------
   (1 row)
-  => select column_name, data_type, column_default, is_nullable
+  => SELECT column_name, data_type, column_default, is_nullable
-        from information_schema.columns where table_name='alpha';
+        FROM information_schema.columns WHERE table_name='alpha';
    column_name | data_type | column_default | is_nullable
   -------------+-----------+----------------+-------------
@@ Line 14: / Line 36: @@
    bar         | character |                | YES
   (2 rows)
-  => select * from information_schema.information_schema_catalog_name;
+  => SELECT * FROM information_schema.information_schema_catalog_name;
    catalog_name
   --------------
    johnd
   (1 row)
-</code>
+== Implementation ==
-As a notable exception among major database systems, Oracle does not currently implement the information schema. There is an open source project trying to make up for that.
+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 that support information_schema include:
+* [[Amazon Redshift]]
+* [[Apache Hive]]
+* [[Microsoft SQL Server]]
+* [[MonetDB]]
+* [[Snowflake Inc.|Snowflake]]
+* [[MySQL]]
+* [[PostgreSQL]]
+* [[H2 (DBMS)|H2 Database]]
+* [[HSQLDB]]
+* InterSystems Caché
+* [[MariaDB]]
+* [[SingleStore]] (formerly MemSQL)
+* [[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]]
+* [[Presto_(SQL_query_engine)|Presto]]
+* [[CrateDB]]
+* [[ClickHouse]]
+* [[CockroachDB]]
+RDBMSs that do not support information_schema include:
+* [[Apache Derby]]
+* [[Apache Ignite]]
+* [[Firebird_(database_server)|Firebird]]
+* [[Microsoft Access]]
+* [[IBM Informix]]
+* [[Ingres_(database)|Ingres]]
+* [[IBM Db2]]
+* [[Oracle Database]]
+* [[SAP HANA]]
+* [[SQLite]]<ref>{{Cite web|title=Pragma statements supported by SQLite|url=https://www.sqlite.org/pragma.html#pragfunc|access-date=2022-02-01|website=www.sqlite.org}}</ref>
+* [[Adaptive_Server_Enterprise|Sybase ASE]]
+* [[SQL_Anywhere|Sybase SQL Anywhere]]
+* [[Teradata]]
+* [[Vertica]]
+== See also ==
+* [[Oracle metadata]]
-==See also==
+== External links ==
+* [http://h2database.com/html/grammar.html#information_schema Information schema in H2 Database]
-*[[Oracle metadata]]
+* [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)]
+* [https://www.sqlite.org/pragma.html#pragfunc Information schema in SQLite]
+* [http://msdn.microsoft.com/en-us/library/ms186778.aspx Information schema in Microsoft SQL Server 2014]
+* [http://msdn.microsoft.com/en-us/library/ms174156 Information schema in Microsoft SQL Server Compact 4.0]
+* [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://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]
-==External links==
+== References ==
+{{reflist}}
-*[http://dev.mysql.com/doc/refman/5.7/en/information-schema.html Information schema in MySQL 5.7]
-*[http://www.postgresql.org/docs/current/interactive/information-schema.html Information schema in PostgreSQL (current version)]
-*[http://www.sqlite.org/cvstrac/wiki?p=InformationSchema Information schema in SQLite]
-*[http://msdn.microsoft.com/en-us/library/ms186778.aspx Information schema in Microsoft SQL Server 2014]
-*[http://msdn.microsoft.com/en-us/library/ms174156 Information schema in Microsoft SQL Server Compact 4.0]
-*[http://sourceforge.net/projects/ora-info-schema/ Oracle Information Schema project on sourceforge]
 [[Category:Databases]]
 [[Category:Computer standards]]
+[[Category:American National Standards Institute standards]]