Learning SQL, 2nd Edition

Chapter 15. Metadata

Along with storing all of the data that various users insert into a database, a database server also needs to store information about all of the database objects (tables, views, indexes, etc.) that were created to store this data. The database server stores this information, not surprisingly, in a database. This chapter discusses how and where this information, known as metadata, is stored, how you can access it, and how you can use it to build flexible systems.

Data About Data

Metadata is essentially data about data. Every time you create a database object, the database server needs to record various pieces of information. For example, if you were to create a table with multiple columns, a primary key constraint, three indexes, and a foreign key constraint, the database server would need to store all the following information:

• Table name

• Table storage information (tablespace, initial size, etc.)

• Storage engine

• Column names

• Column data types

• Default column values

• NOT NULL column constraints

• Primary key columns

• Primary key name

• Name of primary key index

• Index names

• Index types (B-tree, bitmap)

• Indexed columns

• Index column sort order (ascending or descending)

• Index storage information

• Foreign key name

• Foreign key columns

• Associated table/columns for foreign keys

This data is collectively known as the data dictionary or system catalog. The database server needs to store this data persistently, and it needs to be able to quickly retrieve this data in order to verify and execute SQL ...