How to write a query to find all tables in a database that have a specific data

Question

I have to write a query to find all the tables in a database that have a particular value.I want to know where the value is present.I have a database which contain 4000 tables.So I want to find particular table by finding the value. I have values...But donn't know the table names.Is it possible?

Thanking You

Do you know the column_name of the table where the value is stored??? — Praveen, Dec 15 '16 at 11:38

reza ramezani matin · Answer 1 · 2016-12-21T11:46:56.603

You can create a table and name of tables that content of your data is inserted into that table.
create a table:

create table My_table (table_name varchar2(50), column_name varchar2(50));

The following query insert table name into my_table that all of them have your data:

declare
  l_cnt     varchar2(20);
  Query_str varchar2(100);
begin
  FOR i IN (SELECT o.object_Name, OBJECT_TYPE
              FROM user_objects o
             WHERE o.OBJECT_TYPE = 'TABLE') LOOP
    for j in (SELECT column_name
                FROM all_tab_cols
               WHERE table_name = i.object_name) loop

      Query_str := 'select count(*) from ' || i.object_name ||
                   ' where to_char(' || j.column_name || ') = ''[Your_data]''';
      execute immediate Query_str
        into l_cnt;
      if l_cnt > 0 then
        insert into my_table
          (table_name, column_name)
        values
          (i.object_name, j.column_name);
      end if;
    end loop;
  end loop;
end;

after that if any table has your data,my_table shows them
I hope to help you.

I edit query now,please test it – reza ramezani matin Dec 21 '16 at 11:47 — reza ramezani matin, Dec 21 '16 at 11:47

How to write a query to find all tables in a database that have a specific data

1 Answers1