Describing a table in Oracle is a fundamental operation for any database professional, whether you are a developer writing your first query or a database administrator auditing the schema. Understanding how to retrieve metadata about a table provides immediate insight into its structure, constraints, and data types without needing to query the underlying data itself.
Oracle provides several methods to view table definitions, ranging from simple commands accessible via SQL*Plus or SQL Developer to querying the data dictionary views directly. The most common and user-friendly approach involves using the DESCRIBE command, which offers a quick glance at the column names, data types, and nullability of a table.
Using the DESCRIBE Command
The DESCRIBE command, often abbreviated as DESC, is the primary tool for quickly viewing the structure of a table. It is a SQL*Plus and SQLcl command, which means it is interpreted by the client tool rather than the Oracle database kernel itself.
Syntax and Output
To use the command, you simply type DESC followed by the table name. The output is formatted into three columns: Name, Null?, and Type. The Name column lists the column names, the Null? column indicates whether a column can contain null values (usually showing "N" for NOT NULL or leaving it blank for nullable columns), and the Type column displays the Oracle data type and, for character columns, the maximum length.
Querying Data Dictionary Views
For more detailed information or to integrate table descriptions into custom scripts or applications, querying the data dictionary views is the preferred method. These views store the metadata for all objects in the database.
ALL_TAB_COLUMNS and USER_TAB_COLUMNS
The views ALL_TAB_COLUMNS and USER_TAB_COLUMNS provide comprehensive details about table columns. USER_TAB_COLUMNS shows information about tables owned by the current user, while ALL_TAB_COLUMNS shows information about tables accessible to the current user, including those owned by other users.
Key columns in these views include COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, and NULLABLE. By filtering on the TABLE_NAME, you can reconstruct the structure of any accessible table, and you can also retrieve additional metadata such as column defaults or character length semantics.
Inspecting Constraints and Indexes
A complete table description goes beyond columns to include constraints and indexes, which are crucial for understanding data integrity and performance.
Constraints Overview
To see constraints such as primary keys, foreign keys, unique constraints, and check constraints, you should query the USER_CONSTRAINTS view (or ALL_CONSTRAINTS). Joining this view with USER_CONS_COLUMNS allows you to see the specific columns involved in each constraint, providing a clear picture of the relational rules applied to the table.
Viewing Table Creation Scripts
If you need to see the exact SQL statement used to create the table, including all constraints and storage parameters, the DBMS_METADATA package is the most powerful tool available.
DBMS_METADATA Example
By calling the GET_DDL function from this package and specifying the object type as 'TABLE' and the name of your table, Oracle returns the full CREATE TABLE statement. This is invaluable for documentation, migration tasks, or replicating the table structure in another environment, as it provides the complete technical definition as it exists in the database.
Using Oracle SQL Developer
For users working with graphical interfaces, Oracle SQL Developer provides a visual and intuitive way to describe a table.
