Data Dictionary Views with SQL

Data Dictionary Views

Introduction to Data Dictionary Views

Data dictionary views are predefined views provided by the database management system (DBMS) that allow users to retrieve information about database objects (tables, views, indexes, users, etc.). They are essential for understanding the structure and metadata of the database.

Types of Data Dictionary Views

1. System Data Dictionary Views:
   • ALL_: Provides information about objects that the user has access to.
   • USER_: Provides information about objects owned by the current user.
   • DBA_: Provides information about all objects in the database (usually reserved for DBAs).

Querying Various Data Dictionary Views

Views on Tables

These views provide information about the tables in the database.

ALL_TABLES

Contains information about all tables accessible by the user.

Important Columns:

• OWNER: The owner of the table.
• TABLE_NAME: The name of the table.
• TABLESPACE_NAME: The tablespace where the table resides.

Example Query: 

-- List all tables accessible by the current user
SELECT owner, table_name, tablespace_name
FROM all_tables;

USER_TABLES

Contains information about tables owned by the current user.

Example Query: 

-- List all tables owned by the current user
SELECT table_name, tablespace_name
FROM user_tables;

DBA_TABLES

Contains information about all tables in the database (usually for DBAs).

Example Query: 

-- List all tables in the database
SELECT owner, table_name, tablespace_name
FROM dba_tables;

 Views on Columns

These views provide details about the columns in the tables.

ALL_TAB_COLUMNS

Displays columns for tables accessible by the user.

Important Columns:

• OWNER: The owner of the table.
• TABLE_NAME: The name of the table.
• COLUMN_NAME: The name of the column.
• DATA_TYPE: The data type of the column.
• DATA_LENGTH: The length of the column data.

Example Query: 

-- List columns for the table 'EMPLOYEES'
SELECT owner, table_name, column_name, data_type, data_length
FROM all_tab_columns
WHERE table_name = 'EMPLOYEES';

 USER_TAB_COLUMNS

Displays columns for tables owned by the current user.

Example Query: 

-- List columns for the table 'EMPLOYEES' owned by the current user
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

DBA_TAB_COLUMNS

Displays columns for all tables in the database.

Example Query: 

-- List columns for the table 'EMPLOYEES' in the entire database
SELECT owner, table_name, column_name, data_type, data_length
FROM dba_tab_columns
WHERE table_name = 'EMPLOYEES';

 Views on Indexes

These views provide information about indexes created on tables.

ALL_INDEXES

Displays indexes on tables accessible by the user.

Important Columns:

• OWNER: The owner of the index.
• INDEX_NAME: The name of the index.
• TABLE_NAME: The name of the table being indexed.
• UNIQUENESS: Indicates if the index is unique.

Example Query: 

-- List indexes on the table 'EMPLOYEES'
SELECT owner, index_name, table_name, uniqueness
FROM all_indexes
WHERE table_name = 'EMPLOYEES';

 USER_INDEXES

Displays indexes on tables owned by the current user.

Example Query: 

-- List indexes on the table 'EMPLOYEES' owned by the current user
SELECT index_name, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

 DBA_INDEXES

Displays all indexes in the database.

Example Query: 

-- List all indexes on the table 'EMPLOYEES' in the database
SELECT owner, index_name, table_name, uniqueness
FROM dba_indexes
WHERE table_name = 'EMPLOYEES';

Views on Constraints

These views provide information about constraints applied to tables.

ALL_CONSTRAINTS

Displays constraints for tables accessible by the user.

Important Columns:

• OWNER: The owner of the constraint.
• CONSTRAINT_NAME: The name of the constraint.
• TABLE_NAME: The name of the associated table.
• CONSTRAINT_TYPE: The type of constraint (C for CHECK, P for PRIMARY KEY, etc.).

Example Query: 

-- List constraints on the table 'EMPLOYEES'
SELECT owner, constraint_name, constraint_type, table_name
FROM all_constraints
WHERE table_name = 'EMPLOYEES';

USER_CONSTRAINTS

Displays constraints for tables owned by the current user.

Example Query: 

-- List constraints on the table 'EMPLOYEES' owned by the current user
SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

 DBA_CONSTRAINTS

Displays all constraints in the database.

Example Query: 

-- List all constraints on the table 'EMPLOYEES' in the database
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE table_name = 'EMPLOYEES';

Views on Users

These views provide information about database users.

ALL_USERS

Displays information about all users accessible by the current user.

Important Columns:

• USERNAME: The name of the user.
• USER_ID: The ID of the user.
• CREATED: The date when the user was created.

Example Query: 

-- List all users accessible by the current user
SELECT username, user_id, created
FROM all_users;

DBA_USERS

Displays information about all users in the database.

Example Query: 

-- List all users in the database
SELECT username, user_id, created
FROM dba_users;

Views on Privileges

These views provide information about privileges granted on database objects.

ALL_TAB_PRIVS

Displays privileges granted on tables accessible by the user.

Important Columns:

• OWNER: The owner of the table.
• TABLE_NAME: The name of the table.
• GRANTEE: The user or role to whom the privilege is granted.
• PRIVILEGE: The type of privilege (SELECT, INSERT, UPDATE, etc.).

Example Query: 

-- List privileges on the table 'EMPLOYEES'
SELECT owner, table_name, grantee, privilege
FROM all_tab_privs
WHERE table_name = 'EMPLOYEES';

 USER_TAB_PRIVS

Displays privileges on tables owned by the current user.

Example Query: 

-- List privileges on tables owned by the current user
SELECT table_name, grantee, privilege
FROM user_tab_privs;

DBA_TAB_PRIVS

Displays all privileges on tables in the database.

Example Query: 

-- List all privileges on the table 'EMPLOYEES' in the database
SELECT owner, table_name, grantee, privilege
FROM dba_tab_privs
WHERE table_name = 'EMPLOYEES';

Conclusion

Data dictionary views are powerful tools for managing and monitoring database objects. They allow you to query metadata and configuration details, which is essential for database administration. By mastering these views, you can easily obtain information about tables, columns, indexes, constraints, users, and privileges, which aids in effective database management and optimization.