Inspecting Tables and Columns with SQL

Inspecting Tables and Columns

Inspecting tables and columns in Oracle Database is fundamental for understanding the structure of your database and for performing tasks such as data analysis, schema design, and troubleshooting. Oracle Database provides several data dictionary views to help you examine tables and their columns. Below is a detailed guide on how to inspect tables and columns, including examples.

Inspecting Tables

Tables are fundamental database objects that store data in rows and columns. To inspect tables, you can use several data dictionary views that provide information about their structure, including their names, owners, and other attributes.

USER_TABLES

This view provides information about the tables owned by the current user.

Structure:

• TABLE_NAME: Name of the table.
• TABLESPACE_NAME: Name of the tablespace where the table resides.
• CLUSTER_NAME: Name of the cluster if the table is part of a cluster.
• IOT_NAME: Name of the index-organized table if applicable.

Example Query: 

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

ALL_TABLES

This view displays information about all tables accessible to the current user, including those owned by other users.

Structure:

• OWNER: Owner of the table.
• TABLE_NAME: Name of the table.
• TABLESPACE_NAME: Name of the tablespace where the table resides.

Example Query: 

-- List all tables accessible to the current user, including those owned by others
SELECT owner, table_name, tablespace_name
FROM all_tables;

DBA_TABLES

This view provides information about all tables in the database. It requires DBA privileges to access.

Structure:

• OWNER: Owner of the table.
• TABLE_NAME: Name of the table.
• TABLESPACE_NAME: Name of the tablespace where the table resides.
• NUM_ROWS: Estimated number of rows in the table.

Example Query: 

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

Inspecting Columns

Columns define the structure of a table by specifying the data types and constraints for the data stored in the table. To inspect columns, you can use several data dictionary views that provide detailed information about the columns of a table.

USER_TAB_COLUMNS

This view provides information about the columns of the tables owned by the current user.

Structure:

• TABLE_NAME: Name of the table.
• COLUMN_NAME: Name of the column.
• DATA_TYPE: Data type of the column.
• DATA_LENGTH: Length of the column in bytes.
• NULLABLE: Indicates whether the column can accept null values.

Example Query: 

-- List all columns for tables owned by the current user
SELECT table_name, column_name, data_type, data_length, nullable
FROM user_tab_columns
WHERE table_name = 'YOUR_TABLE_NAME';

ALL_TAB_COLUMNS

This view shows information about the columns of all tables accessible to the current user, including those owned by others.

Structure:

• OWNER: Owner of the table.
• TABLE_NAME: Name of the table.
• COLUMN_NAME: Name of the column.
• DATA_TYPE: Data type of the column.
• DATA_LENGTH: Length of the column in bytes.

Example Query: 

-- List all columns for tables accessible to the current user
SELECT owner, table_name, column_name, data_type, data_length
FROM all_tab_columns
WHERE table_name = 'YOUR_TABLE_NAME';

DBA_TAB_COLUMNS

This view provides information about the columns of all tables in the database. Access requires DBA privileges.

Structure:

• OWNER: Owner of the table.
• TABLE_NAME: Name of the table.
• COLUMN_NAME: Name of the column.
• DATA_TYPE: Data type of the column.
• DATA_LENGTH: Length of the column in bytes.
• NULLABLE: Indicates whether the column can accept null values.

Example Query: 

-- List all columns for all tables in the database
SELECT owner, table_name, column_name, data_type, data_length, nullable
FROM dba_tab_columns
WHERE table_name = 'YOUR_TABLE_NAME';

Advanced Column Information

To get more advanced information about columns, such as constraints and indexes associated with them, you can use the following views:

USER_CONS_COLUMNS

This view provides details about the columns involved in constraints defined by the current user.

Structure:

• CONSTRAINT_NAME: Name of the constraint.
• TABLE_NAME: Name of the table.
• COLUMN_NAME: Name of the column.
• POSITION: Position of the column in the constraint.

Example Query: 

-- List columns involved in constraints for tables owned by the current user
SELECT constraint_name, table_name, column_name, position
FROM user_cons_columns
WHERE table_name = 'YOUR_TABLE_NAME';

DBA_INDEXES and DBA_IND_COLUMNS

To inspect indexes and their associated columns, use these views.

DBA_INDEXES Structure:

• INDEX_NAME: Name of the index.
• TABLE_NAME: Name of the table associated with the index.

DBA_IND_COLUMNS Structure:

• INDEX_NAME: Name of the index.
• COLUMN_NAME: Name of the column in the index.
• COLUMN_POSITION: Position of the column in the index.

Example Query: 

-- List all indexes and their columns for a specific table
SELECT i.index_name, c.column_name, c.column_position
FROM dba_indexes i
JOIN dba_ind_columns c
ON i.index_name = c.index_name
WHERE i.table_name = 'YOUR_TABLE_NAME';

Conclusion

Inspecting tables and columns in Oracle Database is crucial for understanding the structure of your database, analyzing data, and performing various administrative tasks. By using the appropriate data dictionary views, you can gather detailed information about tables and columns, including their attributes, constraints, and indexes.