Oracle get table names based on column value


Staff member
I have table like this:


each table is having many columns and one of the column name is employee_id.

Now, I want to write a query which will

1) return all the tables which is having this columns and

2) results should show the tables if the column is having values or empty values by passing employee_id.

e.g. show table name, column name from Table-1, Table-2,Table-3,... where employee_id='1234'.

If one of the table doesn't have this column, then it is not required to show.

I have verified with <a href="">link</a>, but it shows only table name and column name and not by passing some column values to it.

Also verified <a href="" rel="nofollow noreferrer">this</a>, but here verifies from entire schema which I dont want to do it.


Found a solution, but by using xmlsequence which is <a href="" rel="nofollow noreferrer">deprecated</a>,

1)how do I make this code as xmltable?

2) If there are no values in the table, then output should have empty/null. or default as "YES" value

WITH  char_cols AS
  (SELECT /*+materialize */ table_name, column_name
   FROM   cols
  WHERE  data_type IN ('CHAR', 'VARCHAR2') and table_name in ('Table-1','Table-2','Table-3','Table-4','Table-5')) 
SELECT DISTINCT SUBSTR (:val, 1, 11) "Employee_ID",
       SUBSTR (table_name, 1, 14) "Table",
       SUBSTR (column_name, 1, 14) "Column"
FROM   char_cols,
       TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "'
       || column_name
       || '" from "'
       || table_name
       || '" where upper("'
       || column_name
       || '") like upper(''%'
       || :val
       || '%'')' ).extract ('ROWSET/ROW/*') ) ) t ORDER  BY "Table"