Oracle get table names based on column value

Question

I have table like this:

Table-1
Table-2
Table-3
Table-4
Table-5

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 link, but it shows only table name and column name and not by passing some column values to it.

Also verified this, but here verifies from entire schema which I dont want to do it.

UPDATE:

Found a solution, but by using xmlsequence which is deprecated,

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" 
/

I think you may need to do this in two steps - get a list of tables with the column, and then query each of those tables (either separately or with a UNION ALL) to get the data. — bbrumm, Nov 28 '17 at 03:46
The biggest problem you will have with this is that it cannot be done in one pass (in a single SQL query). That is because you must query the tables that have the EMPLOYEE_ID column for an input value, but you must also query the data catalog to find those tables in the first place. The query on the tables, though, must know the names of the tables beforehand. You are looking for "dynamic SQL", an advanced topic - do you feel advanced enough to ask about such things? — , Nov 28 '17 at 04:29
In the second link that you say you verified, the procedure is searching for entire schema using `cols` , which is a synonym for `USER_TAB_COLUMNS` . Why don't you modify the same by adding a where clause condition to include only your tables? — Kaushik Nayak, Nov 28 '17 at 04:46

score 1 · Answer 1 · answered Nov 30 '17 at 05:21

This query can be done in one step using the (non-deprecated) XMLTABLE.

Sample Schema

--Table-1 and Table-2 match the criteria.
--Table-3 has the right column but not the right value.
--Table-4 does not have the right column.
create table "Table-1" as select '1234' employee_id from dual;
create table "Table-2" as select '1234' employee_id from dual;
create table "Table-3" as select '4321' employee_id from dual;
create table "Table-4" as select 1          id from dual;

Query

--All tables with the column EMPLOYEE_ID, and the number of rows where EMPLOYEE_ID = '1234'.
select table_name, total
from
(
    --Get XML results of dynamic query on relevant tables and columns.
    select
        dbms_xmlgen.getXMLType(
            (
                --Create a SELECT statement on each table, UNION ALL'ed together.
                select listagg(
                    'select '''||table_name||''' table_name, count(*) total
                     from "'||table_name||'" where employee_id = ''1234'''
                    ,' union all'||chr(10)) within group (order by table_name) v_sql
                from user_tab_columns
                where column_name = 'EMPLOYEE_ID'
            )
        ) xml
    from dual
) x
cross join
--Convert the XML data to relational.
xmltable('/ROWSET/ROW'
    passing x.xml
    columns
        table_name varchar2(128) path 'TABLE_NAME',
        total      number        path 'TOTAL'
);

Results

TABLE_NAME   TOTAL
----------   -----
Table-1      1
Table-2      1
Table-3      0

score 0 · Answer 2 · answered Nov 28 '17 at 16:25

Just try to use code below. Pay your attention that may be nessecery clarify scheme name in loop. This code works for my local db. set serveroutput on;

DECLARE
  ex_query VARCHAR(300);
  num      NUMBER;
  emp_id number;
BEGIN
  emp_id := <put your value>;
  FOR rec IN
  (SELECT table_name
  FROM all_tab_columns
  WHERE column_name LIKE upper('employee_id')
  )
  LOOP
    num      :=0;
    ex_query := 'select count(*) from ' || rec.table_name || ' where employee_id = ' || emp_id;
    EXECUTE IMMEDIATE ex_query into num;
    if (num>0) then
      DBMS_OUTPUT.PUT_LINE(rec.table_name);
    end if;
  END LOOP;
END;

score 0 · Answer 3 · answered Nov 29 '17 at 06:06

I tried with the xml thing, but I get an error I cannot solve. Something about a zero size result. How difficult is it to solve this instead of raising exception?! Ask Oracle.

Anyway. What you can do is use the COLS table to know what table has the employee_id column.

1) what table from table TABLE_LIKE_THIS (I assume column with table names is C) has this column?

 select * 
   from COLS, TABLE_LIKE_THIS t
  where cols.table_name = t
    and cols.column_name = 'EMPLOYEE_ID' 
 -- think Oracle metadata/ think upper case

2) Which one has the value you are looking for: write a little chunk of Dynamic PL/SQL with EXECUTE IMMEDIATE to count the tables matching above condition

declare
  v_id varchar2(10) := 'JP1829';       -- value you are looking for
  v_col varchar2(20) := 'EMPLOYEE_ID'; -- column
  n_c  number := 0;
begin
  for x in (
   select table_name
     from all_tab_columns cols
        , TABLE_LIKE_THIS t
    where cols.table_name = t.c
      and cols.column_name = v_col
  ) loop
    EXECUTE IMMEDIATE 
    'select count(1) from '||x.table_name
  ||' where Nvl('||v_col||', ''@#'') = ''' ||v_id||''''  -- adding quotes around string is a little specific
    INTO n_c;
    if n_c > 0 then
      dbms_output.put_line(n_C|| ' in ' ||x.table_name||' has '||v_col||'='||v_id);
    end if;
  -- idem for null values
  -- ... ||' where '||v_col||' is null '
  -- or
  -- ... ||' where Nvl('||v_col||', ''@#'') = ''@#'' '
  end loop;
  dbms_output.put_line('done.');
end;
/

Hope this helps

Oracle get table names based on column value

3 Answers3

Linked