SQL-92 INFORMATION SCHEMA

The SQL Standard INFORMATION_SCHEMA, also known as the metadata or the system tables, is where you get to look at the definition of your database. It's not just tables (there are domains in it too plus all the predefined character sets / collations / translations), but the interesting things are in these tables, all of which can be accessed using:
SELECT ... FROM INFORMATION_SCHEMA.table-name

ASSERTIONS are made with CREATE ASSERTION.

CHARACTER_SETS are made with CREATE CHARACTER SET.

CHECK_CONSTRAINTS are made with ALTER TABLE ... CHECK.

COLLATIONS are made with CREATE COLLATION.

COLUMNS are made with CREATE TABLE ... (column definition).

COLUMN_DOMAIN_USAGE show domains columns are based on.

COLUMN_PRIVILEGES are made with GRANT.

CONSTRAINT_COLUMN_USAGE show constraints columns are used in.

CONSTRAINT_TABLE_USAGE show constraints tables are used in.

DOMAINS are made with CREATE DOMAIN.

DOMAIN_CONSTRAINTS are made with CREATE DOMAIN ... CHECK.

INFORMATION_SCHEMA_ CATALOG_NAME shows a single catalog name.

KEY_COLUMN_USAGE shows columns used in primary, unique or foreign keys.

REFERENTIAL_CONSTRAINTS are taken from foreign-key specs.

SCHEMATA is an old-fashioned way to spell "schemas".

SQL_LANGUAGES should contain the words: ISO 9075.

TABLES are made with CREATE TABLE.

TABLE_CONSTRAINTS are made with ALTER TABLE (constraint specification).

TABLE_PRIVILEGES are made with GRANT.

TRANSLATIONS are made with CREATE TRANSLATION.

USAGE_PRIVILEGES are made with GRANT USAGE.

VIEWS are made with CREATE VIEW.

VIEW_COLUMN_USAGE show whence view columns come.

VIEW_TABLE_USAGE show whence view tables come.

Copyright (c) 1997-2002 by Ocelot Computer Services Inc. All rights reserved.

Return to Ocelot home page