How to get the column names and data types of a table in Oracle database
Contents
Method 1: ALL_TAB_COLUMNS
ALL_TAB_COLUMNS is a view in Oracle that contains the information about all columns in all table. We can just query with the table name in this view to get the column names and data types of a table in Oracle.
Query to fetch the colum names and data types
|
1 2 3 4 |
select * from ALL_TAB_COLUMNS where table_name='CUSTOMER' and owner = 'REVISIT_USER1'; |
The above query returns the column names and data types of the table Customer.Since the table is created by owner Revisit_User1 , we have included the condition as owner = ‘REVISIT_USER1’ in the query.
Output:
Method 2: DESCRIBE command
Describe command provides the description of the specified table or view. This command will returns the column name and data type of the table.
Syntax of Describe command in Oracle
|
1 |
Desc { tablename | viewname } |
Example for Describe command
|
1 |
Desc REVISIT_USER1.CUSTOMER; |
The owner_name.table_name given in the desc command to get the column names and data types of the table and it returns the below results for the table Customer.
Output
|
1 2 3 4 5 |
Name Null Type --------------------------------- CUSTOMER_ID NOT NULL NUMBER(19) NAME VARCHAR2 (255 CHAR) ADDRESS VARCHAR2 (255 CHAR) |
