Oracle - how to find columns in a table + stored procedures dependent on them?

Question

Scenario:

I need to list all the columns in a table1 and all stored procedures that depends on those columns of this table1. I need to populate column name and stored procedures to a new table.

I created new_table(col1, sProc) and tried to populate the column name and respective stored procedure on this new_table. Code I wrote is given below:

Declare

Begin

for i in (select column_name from user_tab_columns where lower(table_name) like 'table1') loop

insert into new_table
  select i.column_name, 
        name 
   from user_source 
  where lower(text) like '%' || i.column_name || '%';

commit;

end loop;

end;

Result: The scripts run successfully but the no data is populated on this new_table.

Stress: I tried to solve it for 1 whole day and could not figure it out. Any help on this would be greately appreciated. Thank you once again.

Answer 1

One obvious problem is that you're converting the procedure text to lowercase, but not the column name you're looking for.

This code has other problems, however. What happens if the column name happens to match some part of the text that isn't a column reference?

Answer 2

The best you will be able to do is to list the package name (as that is the value in the USER_SOURCE.NAME field) along with the column. As rexem indicates in his comment, you dont need to resort to a for loop:

 INSERT INTO new_table (col1, sproc) 
    SELECT i.column_name, u.name 
    FROM user_tab_columns i, 
         user_source u 
    WHERE lower(i.table_name) like 'table1' 
      AND lower(u.text) like '%' || lower(i.column_name) || '%';

Answer 3

You can reduce the false positives by including USER_DEPENDENCIES in the query. You may want to restrict the searched types (or alternatively include TYPE in NEW_TABLE).

insert into new_table (col1, sproc)
    select distinct tc.column_name, sp.name     
    from user_tab_columns tc
            , user_source sp
            , user_dependencies d
    where d.referenced_name = 'TABLE1'
    and   d.referenced_type = 'TABLE'
    and   d.type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION'
             , 'PROCEDURE',  'TYPE', 'TRIGGER')
    and   tc.table_name = 'TABLE1'
    and   sp.name = d.name
    and   instr(lower(sp.text), lower(tc.column_name)) > 0
/