Get Table Columns

Your rating: None Average: 2 (1 vote)

Oracle's all_tab_columns table stores meta data information related to table columns. The SQL below will return all columns as well as column order Id for a specific table and owner. In our example, we will be looking at table EMLOYEES and owner HR.

 SELECT column_name,
  data_type        ,
  column_id
   FROM all_tab_columns
  WHERE owner  = 'HR'
AND table_name = 'EMPLOYEES';

Results:

COLUMN_NAME      DATA_TYPE   COLUMN_ID
---------------- ----------  ---------
EMPLOYEE_ID      NUMBER      1        
FIRST_NAME       VARCHAR2    2        
LAST_NAME        VARCHAR2    3        
EMAIL            VARCHAR2    4        
PHONE_NUMBER     VARCHAR2    5        
HIRE_DATE        DATE        6        
JOB_ID           VARCHAR2    7        
SALARY           NUMBER      8        
COMMISSION_PCT   NUMBER      9        
MANAGER_ID       NUMBER      10      
DEPARTMENT_ID    NUMBER      11

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Zooz's picture
User offline. Last seen 11 years 3 weeks ago. Offline
Joined: 06/27/2008
Posts: 10
Re: Get Table Columns

Try this command

DESC <Table Name>;

Lepa's picture
User offline. Last seen 48 weeks 5 days ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Get Table Columns

You are right about the DESC command but this isn't the purpose behind the SQL in the post. The purpose is not to only list the columns BUT to get the actual column names and do something with them. Example would be to dynamically insert column names into your code :)

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog