Table with columns TABLE_NAME and NUMBER_COLUMNS in Oracle

Question

I am working with an Oracle database that has a number of tables. I am being asked to generate a table with metadata about these tables, more precisely, I need a table with columns TABLE_NAME (where each row shows the name of a table) and NUMBER_COLUMNS (where each row shows the number of columns in that table).

I am getting the info on the table names from ALL_TABLES but I can't find a way to associate or even find the number of columns for each table. The methods that I've seen, like this one, only give me a row with the cumulative number of tables.

Please help!

Gordon Linoff · Accepted Answer · 2013-09-03 01:52:00Z

1

You can do this:

select table_name, owner, count(*) as Number_Columns
from all_tab_cols
group by table_name, owner;

edited Sep 3, 2013 at 1:52

answered Sep 3, 2013 at 1:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Glenn Over a year ago

Probably want to group by owner and table_name if dealing with all_tab_cols in case there is a table with the same name in different schemas.

Collectives™ on Stack Overflow

Table with columns TABLE_NAME and NUMBER_COLUMNS in Oracle

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related