How to get all columns' names for all the tables in Oracle?

user_10020470 0
Viewed 2955

would like to know how to get all column names , data type and from which tables in one Oracle database

Below is for MySQL database and i am looking the samething for Oracle database.

select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position
database-schema oracle sql
3 Answers

You can use

SELECT *
  FROM user_tab_cols
 ORDER BY table_name, column_name, column_id

after connecting to the schema from which you want to get the related information.

user_58413060

You can use the following query,

Following Query will give you the tables of a specific schema:

select ut.*,uc.*
  from user_tables ut
  join user_tab_cols uc
    on ut.table_name = uc.table_name
 where lower(ut.table_name) = 'table_name'
 order by ut.table_name,uc.internal_column_id;

Following Query will give you the list of all tables from your current scheam and the tables you are having access to from other schema as well :

select *
  from all_tables ut
  join all_tab_cols uc
    on ut.table_name = uc.table_name
 where ut.owner = '<schema_name_in_upper>'
 order by ut.table_name,uc.internal_column_id;
user_138780230

Get column names and info of all tables owned by the current user

SELECT *
FROM USER_TAB_COLS

Get column names and info of all tables accessible to the current user

SELECT *
FROM ALL_TAB_COLS

Get column names and info of all tables in the entire database

SELECT *
FROM DBA_TAB_COLS

More information can be found in the Oracle documentation

user_130082100
Related