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.
employment_end_date date Employment end date. Null if employee still works for organization.

There are two levels of detail of data dictionary: logical and physical.

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:

select t.name as table_name, c.name as column_name, 
dt.name as data_type, c.max_length as data_length, c.is_nullable
from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
inner join sys.types dt on c.user_type_id = dt.user_type_id
order by t.name, c.column_id

Sample Oracle Data Dictionary Query

Oracle calls it just that – Data Dictionary, and you can access it as a set of views. You can get basic information about tables and their columns with this query:

select t.table_name, c.column_name, c.data_type, 
c.data_length, c.nullable, c.data_default
from user_tables t inner join all_tab_columns c
on t.table_name = c.table_name
order by t.table_name, c.column_id

Oracle has 3 sets of views, distinguished by prefix – USER, ALL and DBA, but let’s not dive into details.

See also: 6 Useful Oracle Data Dictionary Queries Every DBA Should Have

Sample MySQL Data Dictionary Query

Each instance of MySQL holds data in a separate schema named information_schema. You can access it with a SQL-92 standard views:

select t.table_schema, t.table_name, t.engine, 
c.column_name, c.data_type, c.character_maximum_length, c.is_nullable
from information_schema.tables t
inner join information_schema.columns c 
on t.table_schema = c.table_schema and t.table_name = c.table_name
where t.table_type = 'BASE TABLE'
order by t.table_name, c.ordinal_position;

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.

Learn more

  1. Logical vs Physical Data Dictionary
  2. Data Model vs Data Dictionary vs Database Schema vs ERD

or

Start creating Data Dictionaries today