The Information Schema
This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.
Every Catalog in your SQL-environment contains a Schema
INFORMATION_SCHEMA: it contains a series of Views, Assertions and
Domains — together, they allow you to look at (but not
change) the description of every Object that belongs to the Catalog, as though
it was regular SQL-data. The
SELECT Privilege is granted
PUBLIC WITH GRANT OPTION on every View in
intent is that you will only be able to see those rows that describe Objects on
which you have Privileges. The
USAGE Privilege is also granted
PUBLIC WITH GRANT OPTION on every Domain in
INFORMATION_SCHEMA also contains the definition of every built-in SQL
function, that is, every function that you can use as part of SQL (for
CARDINALITY, etc.). The
Privilege is granted to
PUBLIC on each one.
INFORMATION_SCHEMA Views are based on the Tables of an Ur-Schema
DEFINITION_SCHEMA, but the Standard doesn't require it to actually
exist — its purpose is merely to provide a data model
DEFINITION_SCHEMA did exist though,
its Base tables would describe all the Objects and SQL-data available to an
SQL-server at any time — that
DEFINITION_SCHEMA would describe an SQL Cluster.
[NON-PORTABLE] The total number of Views in
INFORMATION_SCHEMA, and their
exact definition, is non-standard because the SQL Standard allows implementors
to add additional Views, as well as to add additional Columns to the Standard-
defined Views, to describe additional, implementation-defined features.
However, minus one exception, the View descriptions that follow must all
be supported by your SQL DBMS.
INFORMATION_SCHEMA is the Schema that contains the Information
Schema Tables, Assertions and Domains. It is considered to have been created by
CREATE SCHEMA statement that includes an
AUTHORIZATION clause showing
an <AuthorizationID> of
INFORMATION_SCHEMA contains one Base table, one Assertion, four
Domains and 56 Views. Their descriptions follow.
INFORMATION_SCHEMA Base tablesThis page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & ...
INFORMATION_SCHEMA AssertionsThis page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & ...
INFORMATION_SCHEMA DomainsThis page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & ...