How to Get All the Column Names in an Oracle Database

Written by andy carr
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Get All the Column Names in an Oracle Database
Oracle stores a data dictionary describing columns in tables. (Hemera Technologies/ Images)

The Oracle data dictionary exists in the form of data views. There are two views relating to the data dictionary and columns in the database. These are called "All_Tab_Cols" and "All_Tab_Columns." The difference is that All_Tab_Cols contains all fields, including system fields. Alternatively, "All_Tab_Columns" hides system fields from queries when they are executed. All_Tab_Columns should be interrogated when only user data is required for inspection. If system data should be included, the All_Tab_Cols view should be queried.

Skill level:


  1. 1

    Call sqlplus by executing the sqlplus icon on your system. This will depend on your version of Oracle and operating system as it can be run on Windows or Linux. This will display a window with the "SQL>" prompt ready to accept commands.

  2. 2

    Type in the following command to extract all column names from the database:

    select column_name from all_tab_cols;

    This will list all column names in the database to be saved or viewed.

  3. 3

    Filter your search by using a "where" clause to obtain column names by other criteria. The full list of fields in the "All_Tab_Columns" table can be obtained by entering the command:

    DESC All_Tab_Columns;

    You can then filter your results by one of the other columns in the table. For example to filter by the sales table name use:

    select column_name from all_tab_cols where table_name = 'sales';

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.