Oracle - SQL to get data from all tables with specific column and value

Question

I have dozen of connected tables with COLUMN_NAME ENTITY_ID and I want to find all related records with specific value, I can find table with specific columns

select * from all_tab_cols a where a.COLUMN_NAME='ENTITY_ID';

And I can find records per table

select * from TABLENAME where ENTITY_ID='100';

Is there a way to view all related records in all tables using single SQL?

Answer 1

I am not sure what is expected so answering as per my understanding as following:

• Let's say we have a table ACCOUNT which has CUST_ID column as following:

SQL> SELECT OWNER, A.TABLE_NAME, COLUMN_NAME FROM
  2      ALL_TAB_COLS A
  3  WHERE
  4      A.COLUMN_NAME = 'CUST_ID';

OWNER      TABLE_NAME           COLUMN_NAME
---------- -------------------- --------------------
TEJASH     ACCOUNT              CUST_ID

SQL> SELECT * FROM ACCOUNT;

    ACC_NR       SUM_    CUST_ID
---------- ---------- ----------
       500       3400        100

• Now, I want to search in all the tables (on which I have access) to find all the tables having a CUST_ID column with value 100 in it.

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1

Here, every table with the column CUST_ID will appear and COUNT column displays the number of records in that table with CUST_ID = 100

• Now, let's add a column to another table and see the effect:

SQL> ALTER TABLE ACTIVE_USERS ADD CUST_ID VARCHAR2(100);

Table altered.

SQL> INSERT INTO ACTIVE_USERS VALUES (5,SYSDATE, SYSDATE, 200);

1 row created.

SQL> SELECT * FROM ACTIVE_USERS;

  CUST_NUM START_DATE           END_DATE             CUST
---------- -------------------- -------------------- ----
########## 21-NOV-19            21-NOV-19            200
########## 21-NOV-19            21-NOV-19
########## 01-JAN-18            01-JAN-19
########## 01-JAN-18
########## 01-JAN-19            01-JUN-19
########## 01-JAN-17            01-MAR-19

6 rows selected.

• Now, Again running our query to find the data from all the tables:

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1
ACTIVE_USERS         CUST_ID                       0

SQL>

And, It worked!!

• Again adding more data to the ACTIVE_USERS table and see the result.

SQL> INSERT INTO ACTIVE_USERS VALUES (6,SYSDATE-1, SYSDATE, 100);

1 row created.

SQL> INSERT INTO ACTIVE_USERS VALUES (7,SYSDATE-2, SYSDATE, 100);

1 row created.

SQL> INSERT INTO ACTIVE_USERS VALUES (8,SYSDATE-3, SYSDATE, 100);

1 row created.

SQL> SELECT * FROM ACTIVE_USERS;

  CUST_NUM START_DATE           END_DATE             CUST
---------- -------------------- -------------------- ----
########## 21-NOV-19            21-NOV-19            200
########## 20-NOV-19            21-NOV-19            100
########## 19-NOV-19            21-NOV-19            100
########## 18-NOV-19            21-NOV-19            100
########## 21-NOV-19            21-NOV-19
########## 01-JAN-18            01-JAN-19
########## 01-JAN-18
########## 01-JAN-19            01-JUN-19
########## 01-JAN-17            01-MAR-19

9 rows selected.

• Let's check the result of our query now.

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1
ACTIVE_USERS         CUST_ID                       3

SQL>

Again, It worked !! :)

Cheers!!