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.
Holds data about organization’s current and past employees. Each row represents one person.
|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.
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.