Retrieve Oracle Table Column Name And Data Type

Query to Get  Oolumn Names and Data Type of  an Oracle  Table

 

Here is at query that retrieves column names and their data type of Oracle tables in a database. I found it handy to have a searchable copy of the source tables and their column name and data types when doing data profiling, modeling and mapping.

 

select owner,

 table_name,
 column_name,
 data_type || decode(data_type,'DATE', ' ', '(' || data_length || decode(data_scale,null,'', ',' || data_scale) || ')' ) as   Data_Type,
nullable,
 data_default
from dba_tab_columns
where owner in('SCOTT','MARY') ;

 

If dba_tab_columns is not available, table user_tab_colimns will also work.

 

Related Notes:

• Find Oracle Table Size, Number Of Columns and Rows
• Find Current Running SQL Statements In Oracle