0

I need to list out all tables in the database where a particular employee made changes to records. I'm looking for a query in oracle to list out all tables where the employee_name column = 'person_name' and for date > 'sample_date'. is this possible ?

sree
  • 1
  • 4
  • Does this answer your question? [Search All Fields In All Tables For A Specific Value (Oracle)](https://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle) – William Robertson Mar 31 '20 at 20:42
  • @william - This is a question to search all tables for a column_name and mine was for coulmn_values. – sree Mar 31 '20 at 21:41
  • He is looking for the value `'1/22/2008P09RR8'` but he doesn't know where it's stored, so he needs a way to query every column. Isn't that similar to your case? – William Robertson Mar 31 '20 at 21:50

1 Answers1

0

As you said - dynamic SQL helps. For example, based on Scott's sample schema, I'm searching for a table that contains both ENAME and HIREDATE columns with desired values (SCOTT and 09.12.1982 (dd.mm.yyyy)).

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    l_str VARCHAR2(500);
  3    l_cnt NUMBER := 0;
  4  BEGIN
  5    FOR cur_r IN (SELECT t.table_name, u1.column_name col1, u2.column_name col2
  6                  FROM user_tables t join user_tab_columns u1 on u1.table_name = t.table_name
  7                                     join user_tab_columns u2 on u2.table_name = t.table_name
  8                  WHERE u1.column_name = 'ENAME'
  9                    AND u2.column_name = 'HIREDATE'
 10                 )
 11    LOOP
 12      l_str := 'SELECT COUNT(*) FROM ' || cur_r.table_name   ||
 13              ' WHERE ' || cur_r.col1  || ' = ''SCOTT'''     ||
 14              '   AND ' || cur_r.col2  || ' = date ''1982-12-09''';
 15
 16      EXECUTE IMMEDIATE (l_str) INTO l_cnt;
 17
 18      IF l_cnt > 0 THEN
 19        dbms_output.put_line(l_cnt ||' row(s) in ' || cur_r.table_name);
 20      END IF;
 21    END LOOP;
 22  END;
 23  /
1 row(s) in EMP

PL/SQL procedure successfully completed.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thank you. Does this provide me with records from the given date to present date ? – sree Mar 31 '20 at 20:48
  • You're welcome. For dates question, change = to >=. – Littlefoot Mar 31 '20 at 21:01
  • I know some tables where these column values are present. Taking your values as example in my db i have 1 table where the value scott exists from date > '02/20/2020'. I don't get any results (0) after executing the cursor though. My date column has time in it. is that a concern ? – sree Mar 31 '20 at 22:58
  • Time shouldn't be a problem, but date format you used might - especially if your "date" values are stored into VARCHAR2 columns. That is usually *bad news* and you'll have to apply TO_DATE function with appropriate format mask. If there are invalid values there (e.g. 34/18/2020), it'll fail so you'd have to fix data first. – Littlefoot Apr 01 '20 at 05:06
  • Display L_STR to verify it, execute it manually, see what happens. – Littlefoot Apr 02 '20 at 20:49
  • Worked Perfectly. I validated against some tables and the row count looked right. Thank you @Littlefoot. – sree Apr 02 '20 at 21:49