Describing Columns in Oracle 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:

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:

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. The following section discusses the characteristics of data dictionary views.

TABLE_NAME	Contains the name of the table
AVG_ROW_LENGTH	A column in the USER_TABLES view
DATA_TYPE	A column in the USER_TAB_COLUMNS view
ALL_TABLES	Lists the tables in all the schemas the current user has access to
COLUMN_NAME	Contains the name of the columns in the USER_TAB_COLUMNS view
USER_TABLES	Lists the tables in the current user's schema

Object Name Thesaurus Ultimately, relational databases should include an object name thesaurus, just as they include a data dictionary. This thesaurus should enforce the naming standards of the company and ensure consistency of name choice and abbreviation (where used). Such standards may require the use of underscores in object naming to make the parsing of the name into component parts a straightforward task. This also helps enforce the consistent use of underscores, rather than the scattered, inconsistent usage within an application that underscores frequently receive now. If you work directly with a government agency or large firm, that organization may already have object-naming standards. The object-naming standards of large organizations have over the years radiated into the rest of the commercial marketplace, and they may form the basis for the naming standards used at your company. For example, those standards may provide the direction to choose between

1. Corporation and
2. Firm.

If they do not, you should develop your naming standards to be consistent, both with those base standards and with the guidelines put forth in this module.