Tables in the Data Dictionary: An Overview

The data dictionary (also called the system catalog or metadata repository) is implemented as a set of system tables that the RDBMS owns and maintains. These tables describe every object in the database: schemas, tables, columns, indexes, keys, permissions, and more. In this lesson, we focus on the tables that make up the data dictionary itself and how they work together to describe your database.

Although names vary by product, the pattern is consistent across all modern relational systems:

• A table (or view) that lists all tables in the database
• A table that describes every column of every table
• Tables that describe indexes and key constraints
• Tables that record users, roles, and permissions
• Tables that describe storage, domains, and other physical details

In many teaching examples these are shown with names such as SYSCATALOG, SYSTABLE, SYSCOLUMN, and SYSFOREIGNKEY. In production systems, you will see vendor-specific names:

• Oracle: data dictionary views such as USER_TABLES, ALL_TAB_COLUMNS, DBA_INDEXES
• PostgreSQL: tables in the pg_catalog schema and the information_schema views
• SQL Server: catalog views such as sys.tables, sys.columns, sys.indexes
• MySQL: the INFORMATION_SCHEMA and performance_schema tables

Regardless of naming, the goal is the same: provide a reliable, queryable description of the database that both humans and the RDBMS itself can use.

A core data dictionary table (often shown conceptually as SYSCATALOG or SYSTABLE) lists every table known to the database. It does not store user data; instead, it stores metadata that tells the RDBMS what user data exists and where.

The sample below shows the first few rows of such a table. Each row describes one table in the database:

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

From this single table, you can answer questions like:

• Which tables exist in this database?
• Who owns each table?
• How many columns does each table have?
• Does a table have a primary key defined?

In many educational examples, SYSCATALOG is treated as another name for the data dictionary. In real systems, the same concept is implemented using different naming conventions but similar structures.

Examples include:

• Oracle: USER_TABLES, ALL_TABLES, DBA_TABLES, plus related views such as USER_TAB_COLUMNS.
• PostgreSQL: tables in pg_catalog combined with INFORMATION_SCHEMA views.
• SQL Server: catalog views like sys.tables, sys.columns, and sys.indexes.
• IBM Db2: catalog tables like SYSCAT.TABLES and SYSCAT.COLUMNS.

While the names differ, they all fulfill the same role as the “tables of the data dictionary”: describing the physical and logical structures in a relational database.

A typical teaching catalog, similar to the one in this course, might list the key data dictionary tables as follows:

creator	tname	dbspace	tabletype	ncols	Primary_key
SYS	SYSTABLE	SYSTEM	TABLE	12	Y
SYS	SYSCOLUMN	SYSTEM	TABLE	14	Y
SYS	SYSINDEX	SYSTEM	TABLE	8	Y
SYS	SYSIXCOL	SYSTEM	TABLE	5	Y
SYS	SYSFOREIGNKEY	SYSTEM	TABLE	8	Y
SYS	SYSKCOL	SYSTEM	TABLE	4	Y
SYS	SYSFILE	SYSTEM	TABLE	3	Y
SYS	SYSDOMAIN	SYSTEM	TABLE	4	Y
SYS	SYSUSERPERM	SYSTEM	TABLE	10	Y
SYS	SYSTABLEPERM	SYSTEM	TABLE	11	Y
SYS	SYSCOLPERM	SYSTEM	TABLE	6	Y

Together, these tables form the “map” of the database: they show what objects exist, how they relate, and who can access them.

Another key data dictionary table is the one that stores column metadata. In our teaching example, this is represented by SYSCOLUMNS or SYSCOL. Every column of every table is represented by exactly one row in this table.

The example below shows characteristics for an Order table:

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

In many commercial RDBMS products, the “SYSCOL-style” table does not store a total count of columns, but you can derive that count with a simple query. For example, a Db2-style catalog might use SYSCOLUMNS:

SELECT COUNT(*) AS total_columns
FROM SYSCOLUMNS;

In systems that expose the SQL Standard INFORMATION_SCHEMA, you can use a similar query:

SELECT COUNT(*) AS total_columns
FROM INFORMATION_SCHEMA.COLUMNS;

The idea is the same: there is a system table or view that contains one row per column, and querying that table gives you insight into the structure of the database.

Other vendors use different names but equivalent structures:

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 and pragma calls

These tables and views are typically read-only; the RDBMS updates them when you execute DDL statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

All of these system tables—those that describe tables, columns, indexes, foreign keys, domains, and permissions—collectively form the data dictionary. They are updated automatically whenever DDL commands are executed, and they provide the single source of truth for tools, applications, and administrators.

By querying these tables, you can:

• Generate documentation for the physical data model
• Audit security and permissions
• Discover unused objects or missing constraints
• Support impact analysis before schema changes

A solid understanding of these data dictionary tables is essential for DBAs and developers who want to manage, optimize, and secure modern relational databases.The next lesson wraps up this module.

[1] data dictionary: The authoritative catalog of database metadata describing every object, its attributes, and its relationships.