Query for columns in oracle database

[Gerri]

Hi,
Can you tell me something about the query on the columns of the tables. I have queried so much on the tables but I need to query on the particular columns of the table. Can you tell how can I do that.Is there any advance procedures to complete this task.Please let me introduce about it.

Thanks

[Reegan]

Query for columns in oracle database :

You can query not only on tables or any other objects of database.you can query on the columns of the particular table in database.There are some defined data dictionary tables for this task.The tables are as follows :

1- USER_TAB_COLUMNS
2- DBA_TAB_COLUMNS
3- ALL_TAB_COLUMNS

These are three most required data dictionary tables which is being used to get information about the columns of particular tables.

[Solomon]

USER_TAB_COLUMNS :

The USER_TAB_COLUMNS data dictionary provided by database management system which is used to view the user level columns and related tables with their respective data types.

Before executing this query,you can see the structure of this particular table using DESC command.

SQL> SELECT table_name, column_name, data_type, data_length
  2  FROM USER_TAB_COLUMNS
  3  WHERE table_name = 'TABLE_NAME'
  4  ORDER BY column_id;

[MrChris-]

DBA_TAB_COLUMNS :

The DBA_TAB_COLUMNS data dictionary view is used to see the columns condition of those tables which is owned by DBA(Database Administrator),meas all the tables of database can be configured by DBA.

The Normal user can't perform this view because it is granted to only DBA by the database.The DBA_TAB_COLUMNS view contains same columns which is used by USER_TAB_COLUMNS but the accessing method is different from each other.

Describes the columns of all tables, views, and clusters using DESC command in the database.

[Calvin K]

ALL_TAB_COLUMNS data:

This view provide same information which is generated by ALL_TAB_COLUMNS but the use and implementations are different.

To collect the statistics for this view, use the SQL ANALYZE statement or the DBMS_STATS package.it provides the information of all the table columns which is owned by every users.

NOTE: DBA_TAB_COLUMNS describes the columns of all tables, views, and clusters in the database,USER_TAB_COLUMNS describes the columns of all tables, views, and clusters owned by the current user. This view does not display the OWNER column.