DBA_TAB_COLS lists all columns in every table and view accessible to the current account, including their data types, nullability, precision, and default values.
Note
DBA_TAB_COLS and DBA_TAB_COLUMNS return identical information. Use DBA_TAB_COLS on clusters running version 1.1.7 or later. For earlier versions, query DBA_TAB_COLUMNS instead. To check your cluster version, see Release notes.
Columns
| Column | Type | Description |
|---|---|---|
owner | CHARACTER VARYING | Username of the table or view owner. |
schema_name | CHARACTER VARYING | Schema that contains the table or view. |
table_name | CHARACTER VARYING | Name of the table or view. |
column_name | CHARACTER VARYING | Name of the column. |
data_type | CHARACTER VARYING | Data type of the column. |
data_length | NUMERIC | Maximum column length, in characters. |
data_precision | NUMERIC | Number of significant digits for NUMBER columns. |
data_scale | NUMERIC | Number of digits to the right of the decimal point for NUMBER columns. |
nullable | CHARACTER(1) | Whether the column accepts null values. Y means the column can be null; N means the column cannot be null. |
column_id | NUMERIC | Ordinal position of the column within the table or view. |
data_default | CHARACTER VARYING | Default value assigned to the column. |
Example
Query all columns in a specific table:
SELECT column_name, data_type, data_length, nullable, data_default
FROM DBA_TAB_COLS
WHERE schema_name = 'myschema'
AND table_name = 'mytable'
ORDER BY column_id;What's next
DBA_TAB_COLUMNS — equivalent view for clusters running version 1.1.7 or earlier