How to create a table with all the table names in my DB and for each one, the number of columns in the table

I’m new at SQL developer so I don’t really know a lot about it. I am trying to obtain (from a select) a table with the names of all the tables in my DB with its number of columns. I have tried to do it but, I can only count the number of columns for each table separately. I can’t obtain the name of each table. Another problem that I found with my code, is that when I try to search in ALL_TABLES, it shows every table that I have access to, not only the ones in the DB that I need.

Try1:

SELECT COUNT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
TABLE_CATALOG = 'database_name' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'table_name'

Answer

This should do the job for you:

SELECT OWNER, TABLE_NAME, COUNT(*) AS COLUMN_COUNT
  FROM ALL_TAB_COLUMNS
  GROUP BY OWNER, TABLE_NAME
  ORDER BY OWNER, TABLE_NAME

You can also use the USER_TAB_COLUMNS or DBA_TAB_COLUMNS views if you have access to them.

sqlfiddle here

Advertisement

Answer