What is Data Dictionary?

Data dictionary is data about data. It’s a description of data structures. It has at least two meanings:

Data Dictionary as a Documentation

One instance of Data Dictionary is a document describing a database schema for users. In most basic case this documentation includes descriptions of tables and their columns as in the example below. Data Dictionary can include various information about data, including relationships, constraints and rules, sources and usage, to name a few. This documentation is used by database users and developers to understand the data and its structures. It can be in form of a simple document or special repository accessed by a dedicated tool.

Example

Table: Employees

Holds data about organization’s current and past employees. Each row represents one person.

Column

Data Type

Description

emlployee_id

int

Primary key of a table

first_name

nvarchar(50)

Employee first name

last_name

nvarchar(50)

Employee last name

nin

nvarchar(15)

National Identification Number

position

nvarchar(50)

Current postion title, e.g. Secretary

department_id

int

Employee deparmtnet. Ref: Departmetns

gender

char(1)

M = Male, F = Female, Null = unknown

employment_start_date

date

Start date of employment in organization. Doesn’t change when position is changed.

Data Dictionary in Database Systems

All major relational database management systems store information about data structures in a special database – predefined schema that holds metadata about each element of a database – tables, columns, indexes, foreign keys, constraints and so on. This information is stored in plain tables and is accessible with a special interface – implemented as views, functions, stored procedures or other methods.

There is a standard interface to data dictionary defined in SQL-92 called Information schema. This is a predefined set of views that a database system must provide to access its metadata. Example of databases that support Information schema are SQL Server, MySQL and PostgreSQL. Databases that do not support it are Oracle, DB2 and SQLite. Each database has its proprietary interface regardless of Information schema.

Here are examples of interfaces from major database systems:

Sample SQL Server Catalog Query

SQL Server, aside Information schema, has a recommended interface called System Catalog and it is also in a form of set of database views. Here is a sample query listing tables and their columns:

Both Data Dictionary types describe data structures but the first one is intended for people and the latter for machines.

Data Dictionary and database consoles

Fun fact: each database console, like SQL Server’s Management Studio or Oracle’s SQL Developer, use one of Data Dictionary interfaces to access your database schema to list available tables, views, their columns, and so on.