Listing All Tables and Columns from my Database (ORACLE)

Question

I would like to know how it is possible to list all the tables and columns from my ORACLE database but by "grouping" all this information into only one table? A table with two columns: one with the name of the table and the other one with all the colums in that table. Thank you.

For instance:

TABLE_NAME COLUMNS

TABLE A => LIST OF COLUMNS

TABLE B => LIST OF COLUMNS

TABLE C => LIST OF COLUMNS

This is how I list all the tables from my ORACLE database:

SELECT TABLE_NAME FROM USER_TABLES;

This is how I list all the columns from one of the tables from my ORACLE database:

SELECT column_name
FROM user_tab_cols
WHERE table_name = 'TABLE_NAME';

what's wrong with user_tab_cols. you found the 2 columns table_nameand column_name already. so what are you missing? — A ツ
– A ツ, Commented Apr 20, 2016 at 14:55
@Ariana: Aツ is right. But if you want to know other info present only in user_tab_cols you can JOIN two tables using table_name field — Joe Taras
– Joe Taras, Commented Apr 20, 2016 at 15:01
I updated my post. What I want is "grouping" the information of the two selects into one table. — Ariana
– Ariana, Commented Apr 20, 2016 at 15:05
Join the tables and order the results by table_name. If you really need a single row for each table, use something more suitable to re-arrange the results. For example, I could do it in 5 minutes using ColdFusion. — Dan Bracuk
– Dan Bracuk, Commented Apr 20, 2016 at 15:05
looks like your question is not your question. string aggregate oracleis what you should search with your favourite tool. — A ツ
– A ツ, Commented Apr 20, 2016 at 15:14

Vamsi Prabhala · Accepted Answer · 2016-04-20 15:30:41Z

3

Use listagg to get all the column names in a table as a list.

create table table_column_list as 
SELECT table_name,listagg(column_name,',') within group(order by column_id) column_list
FROM user_tab_cols
group by table_name;

answered Apr 20, 2016 at 15:30

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ariana Over a year ago

Meanwhile, this is exactly the select I made up. Thanks anyway :)

Collectives™ on Stack Overflow

Listing All Tables and Columns from my Database (ORACLE)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related