This content has been marked as final. Show 4 replies
Hi Ramesh1 person found this helpful
You can use DBA_TAB_COLUMNS table.
Mahir M. Quluzade
I assume you want to know the number of columns per table and not just the number of columns.1 person found this helpful
You can use the datadictionary view dba_tab_cols
The number of rows returned will give you the number of tables in the schema , and against each table you will have a count of the number of columns.
select table_name, count(column_name) from dba_tab_cols where owner = 'SCHEMA_NAME' group by table_name
You may have to give your user explicit privileges to select from dba_tab_cols
try this1 person found this helpful
CREATE TABLE USER_TABLE_COLUMS(TABLE_NAME varchar2(50), COLUMN_NAME varchar2(50), DATA_TYPE varchar2(50));
insert into USER_TABLE_COLUMS (select TABLE_NAME, COLUMN_NAME, DATA_TYPE from all_tab_columns where ower = ur_schema)
FROM schema.USER_TAB_COLS b, all_user_columns C
where b.TABLE_NAME = c.TABLE_NAME
and b.COLUMN_NAME = c.COLUMN_NAME
and b.DATA_TYPE <> c.DATA_TYPE;
The reason I used dba_tab_cols is that it will get all the tables in the database for the schema specified.
If you use user_tab_cols you only get tables in the schema select from. If you use all_tab_cols you only get tables in your schema and tables that you have privileges to select from, therefore its possible to get an incorrect count using all_tab_cols view.