title: Oracle get table names based on column value
body: <p>I have table like this:</p>
  
  <pre><code>Table-1
  Table-2
  Table-3
  Table-4
  Table-5
  </code></pre>
  
  <p>each table is having many columns and one of the column name is employee_id.</p>
  
  <p>Now, I want to write a query which will </p>
  
  <p>1) return all the tables which is having this columns and </p>
  
  <p>2) results should show the tables if the column is having values or empty values by passing employee_id.</p>
  
  <p>e.g. show table name, column name from Table-1, Table-2,Table-3,... where employee_id='1234'.</p>
  
  <p>If one of the table doesn't have this column, then it is not required to show. </p>
  
  <p>I have verified with <a href="https://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle">link</a>, but it shows only table name and column name and not by passing some column values to it.</p>
  
  <p>Also verified <a href="https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles-in-an-entire-schema/" rel="nofollow noreferrer">this</a>, but here verifies from entire schema which I dont want to do it. </p>
  
  <blockquote>
    <p>UPDATE:</p>
  </blockquote>
  
  <p>Found a  solution, but by using xmlsequence which is <a href="https://docs.oracle.com/cloud/latest/db112/SQLRF/functions251.htm#SQLRF06170" rel="nofollow noreferrer">deprecated</a>, </p>
  
  <p>1)how do I make this code as xmltable? </p>
  
  <p>2) If there are no values in the table, then output should have empty/null. or default as "YES" value</p>
  
  <pre><code>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" 
  / 
  </code></pre>