How to Order SQL Tables by Catalog

By Allen G. Taylor

For really large database systems, multiple SQL schemas may not be sufficient. In a large distributed database environment with many users, you may even find duplicated schema names. To prevent this situation, SQL adds another level to the containment hierarchy: the catalog. A catalog is a named collection of schemas.

You can qualify a table name by using a catalog name and a schema name. This safeguard is the best way to ensure that no one confuses the table in one schema with a table that has the same name in some other schema that has the same schema name. The catalog-qualified name appears in the following format:

CATALOG_NAME.SCHEMA_NAME.TABLE_NAME

At the top of the database containment hierarchy are clusters. Systems rarely require use of the full scope of the containment hierarchy; going to the catalog level is enough in most cases. A catalog contains schemas; a schema contains tables and views; tables and views contain columns and rows.

The catalog also contains the information schema. The information schema contains the system tables. The system tables hold the metadata associated with the other schemas. The metadata contained in the system tables is what makes the database self-describing.

Because catalogs are identified by name, you can have multiple catalogs in a database. Each catalog can have multiple schemas, and each schema can have multiple tables. Of course, each table can have multiple columns and rows.

image0.jpg