Information schema

In relational databases, the information schema is an ANSI standard set of read-only views which provide information about all of the tables, views, columns, 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 SHOW command of MySQL, the DESCRIBE command of Oracle, and the \d command of PostgreSQL.

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

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.

See also

• Oracle metadata

External links

• Information schema in MySQL 5.7
• Information schema in PostgreSQL (current version)
• Information schema in SQLite
• Information schema in Microsoft SQL Server 2005
• Information schema in Microsoft SQL Server Compact 4.0
• Oracle Information Schema project on sourceforge