Physical Design   «Prev  Next»

Lesson 16Data Dictionary Tables
Objective Describe the Tables that are part of the Data Dictionary

Tables in the Data Dictionary: An Overview

The data dictionary is a critical component of a database management system (DBMS), consisting of tables that store metadata about the database's structure and objects. These tables provide valuable information about tables, columns, indexes, constraints, and other database elements. This document describes the various tables that are part of the data dictionary, emphasizing their importance and the information they store.
  1. Introduction: The data dictionary, also known as the system catalog or metadata repository, is a collection of tables within a DBMS that stores information about the database's structure, objects, and operations. It serves as a centralized resource for database administrators, developers, and applications to query and manage metadata. This document explores the different tables that constitute the data dictionary, highlighting their significance and the data they contain.
  2. Tables in the Data Dictionary: The data dictionary comprises various tables, each dedicated to storing specific metadata about database elements. Some common tables in the data dictionary include:
    • Table Information: This table stores essential information about each table in the database, such as the table name, schema, creation time, and other relevant properties.
    • Column Information: This table contains details about each column within a table, including the column name, data type, size, nullable status, and default values, if any.
    • Index Information: The index information table provides metadata about the database indexes, including index names, associated table and columns, index type (e.g., clustered or non-clustered), and other attributes.
    • Constraint Information: This table maintains information about the various constraints applied to database objects, such as primary keys, foreign keys, unique constraints, and check constraints. The constraint information table includes details like constraint name, type, and columns involved
    • User Information: The user information table holds data about database users, their roles, and privileges. This table is crucial for managing database access and ensuring data security.
    • Stored Procedure and Function Information: This table stores metadata about stored procedures and functions within the database, such as names, input and output parameters, and associated schema.
    • View Information: The view information table contains details about database views, including view names, schema, and the underlying SQL query used to define the view.
    • Trigger Information: This table provides metadata about database triggers, such as trigger names, associated tables, triggering events (e.g., INSERT, UPDATE, DELETE), and trigger action statements.
  3. Importance of the Data Dictionary: The data dictionary plays a vital role in database management by:
    • Providing Metadata Access: The data dictionary allows database administrators, developers, and applications to access metadata about the database structure and objects, enabling efficient schema management and query optimization.
    • Ensuring Data Consistency: By maintaining a centralized repository of metadata, the data dictionary helps ensure data consistency across the database and prevents issues caused by redundant or outdated metadata.
    • Facilitating Data Security: The data dictionary tables related to user information, roles, and privileges are essential for managing database access, implementing security policies, and auditing user actions.

SYSCATALOG lists the tables in the Database

The data dictionary[1] will contain several tables listing the tables, columns, indexes, and permissions comprising the database. The following diagram shows the first few rows of two tables in a data dictionary. The first, SYSCATALOG, lists the tables in the database.
System Catalog

The SYSCATALOG data dictionary table.
  1. Creator column: The Creator column shows which user created a table.
  2. TName column: The Tname column contains the names of the tables in the database.
  3. DBSpace: The DBSpace column shows where each table is stored on the computer.
  4. TableType: The TableType column shows the type of each table (e.g., base table, temporary table).
  5. Ncols: The number of columns in a table.
  6. PrimaryKey: Whether the table has a primary key field or fields.

Creator TName DBSpace TableType NCols PrimaryKey
SYS SYSTABLE SYSTEM TABLE 10 Y
SYS SYSCOLUMN SYSTEM TABLE 11 Y
SYS SYSINDEX SYSTEM TABLE 6 Y
SYS SYSINDXCOL SYSTEM TABLE 4 Y
SYS SYSFOREIGNKEY SYSTEM TABLE 7 Y

Data Dictionary Tables

The "SYSCATALOG" in a relational database is often referred to as the "data dictionary". The data dictionary is a collection of metadata that includes information about the database objects, such as tables, columns, data types, indexes, constraints, and more. It essentially describes the structure of the database. The SYSCATALOG or system catalog typically contains several system tables that store this metadata. In many relational database management systems (RDBMS), these system tables are automatically maintained by the database and are essential for its operation. The data dictionary provides critical information used by the database engine and by users querying the database. Different RDBMS might have specific names for their data dictionary tables, but the concept remains consistent across different systems.
For example:
  • In Oracle, the data dictionary is often referred to as the Oracle Data Dictionary.
  • In PostgreSQL, the system catalog tables are stored in the `pg_catalog` schema.
  • In SQL Server, the equivalent is often referred to as the System Views.

Thefore, SYSCATALOG is indeed another name for what is commonly known as the data dictionary.

Sample Data Dictionary Tables

The precise tables that make up a data dictionary depend somewhat on the DBMS. The keystone of the data dictionary is actually a table that documents all the data dictionary tables (often named syscatalog, the first few rows of which can be found in Figure 6-16). From the names of the data dictionary tables, you can probably guess that there are tables to store data about base tables, their columns, their indexes, and their foreign keys.
Figure 6-16: Syscatalog consisting of 1) creator 2) tname 3) dbspace 3) tabletype 4) ncols 5) Primary_key
Figure 6-16: Syscatalog consisting of 1) creator 2) tname 3) dbspace 3) tabletype 4) ncols 5) Primary_key

The following diagram below discusses the SYSCOLUMNS characteristics.
SYSCOLUMNS characteristics
SYSCOLUMNS characteristics

SYSCOL Table contains Metadata

The SYSCOL table, or its equivalent in different relational database management systems (RDBMS), contains metadata about the columns in the database. While it does not directly store the total number of columns within the data dictionary, you can derive this information by querying the SYSCOL table or its equivalent. For example, in IBM Db2, the SYSCOLUMNS table stores column information, and you can obtain the total number of columns in the database with the following SQL query: 
SELECT COUNT(*) as total_columns
FROM SYSCOLUMNS;

In other RDBMS platforms, the table or view that stores column metadata might have a different name, such as INFORMATION_SCHEMA.COLUMNS in MySQL, PostgreSQL, or SQL Server. In these systems, you can use a similar query to obtain the total number of columns: 
SELECT COUNT(*) as total_columns
FROM INFORMATION_SCHEMA.COLUMNS;

In summary, the SYSCOL table or its equivalent in various RDBMS platforms contains metadata about the columns in the database. By querying this table, you can derive the total number of columns within the data dictionary, although the table itself does not store this count directly.
Many other database management systems use a system table or view that is similar to the Oracle SYSCOL table to store information about the columns in tables in the database. Some examples include:
  1. Microsoft SQL Server: sys.columns
  2. MySQL: information_schema.columns
  3. PostgreSQL: information_schema.columns
  4. IBM DB2: syscat.columns
  5. SQLite: sqlite_master
The specific name and contents of the table or view may vary depending on the database system, but it typically contains information such as the column name, data type, nullability, and default value. In addition to these system tables or views, some database systems also provide other ways to view information about the columns in tables. For example, in Microsoft SQL Server, you can use the sp_helpcolumn stored procedure to get detailed information about the columns in a specific table. It is important to note that the system tables or views that contain information about columns are typically read-only. This is because they are used by the database system to manage the database, and modifying them could cause unexpected problems.
SYSCOL table consisting of the columns listed below
SYSCOL table consisting of the columns listed below
The SYSCATALOG data dictionary table.
  1. Creator: The Creator column shows which user created a table.
  2. Cname: The CName
  3. TName: The TName column contains the names of the columns in the database.
  4. ColType: The ColType column contains the data domain for each column in the database.
  5. Nulls: The Nulls field contains a yes or no value indicating whether a field can contain Null values or not.
  6. Length: The Length column indicates the number of bytes used to store the column's values.
  7. InPrimaryKey: The InPrimaryKey column indicates whether or not a column is part of its table's primary key.
  8. ColNo: The ColNo column contains the number of the column in the database (assigned by the RDBMS).
Creator CName TName ColType Nulls Length InPrimaryKey ColNo
DBA OrderNo Order INT N 4 Y 1
DBA OrderDate Order Date N 6 N 2
DBA CDNo Order INT N 4 N 3
DBA OrderCost Order Numeric N 6 N 4
DBA Received Order Binary N 1 N 5

The second, SYSCOLUMNS, lists the characteristics of every table column in the database.

System Tables containing Metadata

Metadata about the actual data structures themselves should be able to be selected from system tables, usually called system catalogs. For example, in Oracle these tables make up the Oracle Data Dictionary. These catalogs or library tables contain the key pieces of data about the Physical model in data element form. Some even store the definitions of the tables and columns. This emphasizes that the data model and database structures are available for public use.
The data dictionary is a critical component of a DBMS, comprising tables that store metadata about various database elements, such as tables, columns, indexes, constraints, and more. These tables play a crucial role in providing metadata access, ensuring data consistency, and facilitating data security. A comprehensive understanding of the tables in the data dictionary enables database administrators and developers to better manage, optimize, and secure their database systems.
The next lesson wraps up this module.

[1]data dictionary: A list of descriptions of data items to help developers stay on the same track.

SEMrush Software