| Lesson 6 | Describing columns |
| Objective | Gather information about the columns in a table. |
You can query the data dictionary views to get information about the
columns in a particular table.
In an Oracle database, the basic column information is kept in the data dictionary view called USER_TAB_COLUMNS, for the columns in the tables owned by your schema. The most common columns in this particular view are:
In an Oracle database, the basic column information is kept in the data dictionary view called USER_TAB_COLUMNS, for the columns in the tables owned by your schema. The most common columns in this particular view are:
| TABLE_NAME | The name of the table as shown in the TABLE_NAME column of the USER_TABLES data dictionary view |
| COLUMN_NAME | The name of the column |
| DATA_LENGTH | The maximum length of the data in the column, regardless of data type |
| CHAR_COL_DECL_LENGTH | The declared length for character columns |
| DATA_TYPE | The data type of the column |
| DATA_PRECISION and DATA_SCALE | Shows the precision and scale of numeric columns |
| NULLABLE | Contains a value of "N" if the column does not allow NULL values |
The statistics for each column are kept in a separate data dictionary view, USER_TAB_COL_STATISTICS.
This view includes detailed information about the values in a column, such as the number of distinct values in the column, the number of NULL values in the column, and the lowest and highest values in the column.
These statistics are gathered by running the ANALYZE command on the table containing the columns.
You often will want to get information about all the columns in a range of tables.
To do this, you can create a query that joins the USER_TABLES view and the USER_TAB_COLUMNS view, such as:
This view includes detailed information about the values in a column, such as the number of distinct values in the column, the number of NULL values in the column, and the lowest and highest values in the column.
These statistics are gathered by running the ANALYZE command on the table containing the columns.
You often will want to get information about all the columns in a range of tables.
To do this, you can create a query that joins the USER_TABLES view and the USER_TAB_COLUMNS view, such as:
SELECT USER_TABLES.TABLE_NAME, USER_TAB_COLUMNS.COLUMN_NAME FROM USER_TABLES,USER_TAB_COLUMNS WHERE USER_TABLES.TABLE_NAME = USER_TAB_COLUMNS.TABLE_NAME ORDER BY USER_TABLES.TABLE_NAME
This returns all the tables for the user along with their columns.
The next lesson teaches you how to modify a table that already exists in your Oracle database.
Data Dictionary Views
The next lesson teaches you how to modify a table that already exists in your Oracle database.
Click the link below to read about the characteristics in the data dictionary views.
Data Dictionary Views
Data Dictionary Views
