1

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.

2
  • You can't mix data and metadata. You'll need to use dynamic SQL, ie generate your SQL from the metadata, and then execute it. Jan 17, 2019 at 11:46
  • What's the point in including the value column in the expected result? That will never be different to the one you want to put in the WHERE clause. What if there are multiple rows where campus_id = 1 in those tables? What if there are no rows at all that match that condition?
    – user1822
    Jan 17, 2019 at 12:38

2 Answers 2

0

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

0
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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.