Accessing Metadata

The dictionary or catalog stores information about the database itself. This is data about data or 'metadata'.

It holds:

• Description of database objects (tables, users, rules, views, indexes,..)

• Information about who is using what data (locks)

• Schemas and mapping

• The dictionary itself

For example, consider the following relations:

ACTOR(AID, FIRSTNAME, LASTNAME)

MOVIE(MID, MNAME, BUDGET, GROSS)

ACTED_IN(AID, MID, STARRING, WAGE)

Listing all the tables that exist in the 'test' schema:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TEST'

Listing all tables that have more than 3 columns:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'TEST'
GROUP BY TABLE_NAME
HAVING COUNT(*) > 3

Listing the first name and last name of all actors whose last name is equal to the name of an attribute in MOVIE:

SELECT FIRSTNAME, LASTNAME
FROM TEST.ACTOR, INFORMATION_SCHEMA.COLUMNS I
WHERE TABLE_SCHEMA = 'TEST'
AND I.TABLE_NAME = 'MOVIE'
AND LASTNAME = I.COLUMN_NAME