CODEWITHSUNDEEP
oracle-database
user2849710
user2849710

Reputation: 71

How can I display the all the table names, along with their count of column names?

My database has a lot of tables. How can I display all the table names along with the count of column names in each table?

myoutput:

------------
table_name       count(*)
----------       --------
table_t1            12
x_a                  5
Y_k                 23
samptabl             0

Upvotes: 0

Views: 1604

Answers (4)

user2986578
user2986578

Reputation: 1

If you want to know all table_names and columns count in your Entire Database then here is the query.

Query : SELECT TABLE_NAME,COUNT(COLUMN_NAME) as No_Of_Cols FROM ALL_TAB_COLS GROUP BY TABLE_NAME;

Thanks, Venu.

Upvotes: 0

Ruban J
Ruban J

Reputation: 622

SELECT table_name, count(*) column_count FROM all_tables GROUP BY table_name;

Upvotes: -1

Rachcha
Rachcha

Reputation: 8816

If you want the name of the table with the number of columns in it, use DBA's answer here.

If you want name of the table and the number of rows in it, use the following:

SELECT table_name, num_rows
  FROM user_tables;

The numbers of rows in this query represent the numbers when the table was last analyzed. To return the latest numbers run ANALYSE tablename before running this query.

Upvotes: 0

Dba
Dba

Reputation: 6639

Use USER_TAB_COLS view to get the column_count.

SELECT table_name, count(*) column_count
FROM   user_tab_cols
GROUP BY table_name;

Upvotes: 2

Related Questions