Information schema

Last updated

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:

Contents

 => 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 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:

See also

Related Research Articles

Structured Query Language (SQL) is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.

<span class="mw-page-title-main">Data definition language</span> Syntax for defining data structures

In the context of SQL, data definition or data description language (DDL) is a syntax for creating and modifying database objects such as tables, indices, and users. DDL statements are similar to a computer programming language for defining data structures, especially database schemas. Common examples of DDL statements include CREATE, ALTER, and DROP. If you see a .ddl file, that means the file contains a statement to create a table. Oracle SQL Developer contains the ability to export from an ERD generated with Data Modeler to either a .sql file or a .ddl file.

An SQL INSERT statement adds one or more records to any single table in a relational database.

ADO.NET is a data access technology from the Microsoft .NET Framework that provides communication between relational and non-relational systems through a common set of components. ADO.NET is a set of computer software components that programmers can use to access data and data services from a database. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems, though it can also access data in non-relational data sources. ADO.NET is sometimes considered an evolution of ActiveX Data Objects (ADO) technology, but was changed so extensively that it can be considered an entirely new product.

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

Multi-master replication is a method of database replication which allows data to be stored by a group of computers, and updated by any member of the group. All members are responsive to client data queries. The multi-master replication system is responsible for propagating the data modifications made by each member to the rest of the group and resolving any conflicts that might arise between concurrent changes made by different members.

A relational database management system uses SQL MERGE statements to INSERT new records or UPDATE existing records depending on whether condition matches. It was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.

A spatial database is a general-purpose database that has been enhanced to include spatial data that represents objects defined in a geometric space, along with tools for querying and analyzing such data.

An entity–attribute–value model (EAV) is a data model optimized for the space-efficient storage of sparse—or ad-hoc—property or data values, intended for situations where runtime usage patterns are arbitrary, subject to user variation, or otherwise unforeseeable using a fixed design. The use-case targets applications which offer a large or rich system of defined property types, which are in turn appropriate to a wide set of entities, but where typically only a small, specific selection of these are instantiated for a given entity. Therefore, this type of data model relates to the mathematical notion of a sparse matrix. EAV is also known as object–attribute–value model, vertical database model, and open schema.

The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. It is suitable for use in selecting a pseudo column such as SYSDATE or USER.

The SQuirreL SQL Client is a database administration tool. It uses JDBC to allow users to explore and interact with databases via a JDBC driver. It provides an editor that offers code completion and syntax highlighting for standard SQL. It also provides a plugin architecture that allows plugin writers to modify much of the application's behavior to provide database-specific functionality or features that are database-independent. As this desktop application is written entirely in Java with Swing UI components, it should run on any platform that has a JVM.

An embedded database system is a database management system (DBMS) which is tightly integrated with an application software; it is embedded in the application. It is a broad technology category that includes:

A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures.

Apache Empire-db is a Java library that provides a high level object-oriented API for accessing relational database management systems (RDBMS) through JDBC. Apache Empire-db is open source and provided under the Apache License 2.0 from the Apache Software Foundation.

PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database, TimesTen in-memory database, and IBM Db2. Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

SymmetricDS is open source software for database and file synchronization with Multi-master replication, filtered synchronization, and transformation capabilities. It is designed to scale for a large number of nodes, work across low-bandwidth connections, and withstand periods of network outage. Data synchronization occurs asynchronously from a scheduled job, with data changes being sent over a push or pull operation. It uses standard web protocols (HTTP) and database technologies (JDBC) in order to support a wide range of platforms and maximize its interoperability. It includes support for Oracle, MySQL, MariaDB, PostgreSQL, Greenplum, SQL Server, SQL Server Azure, HSQLDB, H2, Derby, DB2, Firebird, Informix, Interbase, SQLite, Sybase ASE, Sybase ASA, MongoDB, Amazon_Redshift, and VoltDB databases.

<span class="mw-page-title-main">Flyway (software)</span>

Flyway is an open-source database-migration tool.

<span class="mw-page-title-main">DBeaver</span> Multi-platform database administration software

DBeaver is a SQL client software application and a database administration tool. For relational databases it uses the JDBC application programming interface (API) to interact with databases via a JDBC driver. For other databases (NoSQL) it uses proprietary database drivers. It provides an editor that supports code completion and syntax highlighting. It provides a plug-in architecture that allows users to modify much of the application's behavior to provide database-specific functionality or features that are database-independent. It is written in Java and based on the Eclipse platform.

References

  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.


This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.