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'
Advertisement
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.