0

how can I search for a matching string in all columns of a table and its child tables which has foriegn key relationship and return the result.

Please someone, give me the oracle query for this.

Regards

af_khan
  • 1,030
  • 4
  • 25
  • 49

1 Answers1

0

try this:

SELECT 'select * from ' || table_name || ' where ' || clause
  FROM (SELECT DISTINCT REPLACE(stragg(column_name)
                                  over(PARTITION BY table_name ORDER BY table_name), ',',
                                  ' = ''__your_string__'' OR ') || ' = __your_string__' clause,
                         table_name
           FROM user_tab_columns
          WHERE table_name IN
                (SELECT table_name
                   FROM all_constraints
                  WHERE constraint_type = 'R'
                    AND r_constraint_name IN (SELECT constraint_name
                                                FROM all_constraints
                                               WHERE constraint_type IN ('P', 'U')
                                                 AND table_name = '__yout_table__')));

Additionally you have to wrap it in a PLSQL or to loop in someway to execute each row of the record set.

i100
  • 4,529
  • 1
  • 22
  • 20
  • ORA-00904: "STRAGG": invalid identifier ...i am facing this with your query – af_khan Feb 26 '14 at 18:33
  • I tried using sys.stragg and it worked but still no results are fetched. – af_khan Feb 26 '14 at 18:40
  • Did you replace __your_table__ with desired table name and __your_string__ with sought string? Which version of Oracle do you use? – i100 Feb 26 '14 at 19:12
  • I replaced as per your instruction but it dint work. Using oracle 11g – af_khan Feb 26 '14 at 19:58
  • your recordset is empty or you receive any error? Try executing select statements one by one. If recordset is empty this should mean that perhaps __your_table__ has not foreign keys... – i100 Feb 27 '14 at 06:42