Select Column Values from all the tables of database ORACLE

Question

I am trying to fetch column value from all the tables of database where column value matches.

Table A
CAMPUS_ID
1

Table B
CAMPUS_ID
1

Expected Result

TABLE   VALUE
A       1 
TABLE   VALUE
B       1 

Query

SELECT CAMPUS_ID FROM (SELECT A.TABLE_NAME FROM
user_tables A JOIN user_tab_columns C ON C.TABLE_NAME = 
A.TABLE_NAME WHERE C.COLUMN_NAME = 'CAMPUS_ID')
WHERE CAMPUS_ID = '1'

Error Invalid Identifier

Inner query return the correct tables.

Answer 1

You can do something like this:

select table_name, 
       extractvalue(
          xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name||' where campus_id = 1')),
         '/ROWSET/ROW/C'
       ) as count
from user_tables ut
where exists (select *
              from user_tab_columns c 
              where c.table_name = ut.table_name
                and c.column_name = 'CAMPUS_ID');

dbms_xmlgen.getxml runs the passed SQL statement and returns the result as an XML value. extracvalue() is then used to extract the actual rowcount.

This will return the table name and the count of rows in that table where campus_id = 1

Answer 2

select table_name from dba_tab_columns where column_name='THE_COLUMN_YOU_LOOK_FOR';

If you don't have DBA privileges, you can try this:

select table_name from all_tab_columns where column_name='THE_COLUMN_YOU_LOOK_FOR';