Information Schema
Encyclopedia
In relational database
s, the information schema is an ANSI
standard set of read-only views which provide information about all of the table
s, view
s, column
s, and 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
, the
, and the
.
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
s, the information schema is an ANSI
Ansi
Ansi is a village in Kaarma Parish, Saare County, on the island of Saaremaa, Estonia....
standard set of read-only views which provide information about all of the table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
s, view
View (database)
In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
s, column
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
s, and procedures
Stored procedure
A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.Typical uses for stored procedures include data validation or access control mechanisms...
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
SHOW command of MySQLMySQL
MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
, the
DESCRIBE command of OracleOracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
, and the
\d command of PostgreSQLPostgreSQL
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
.
=> 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)