How to find out which procedures and functions are using a table?

Question

I'm deleting a table (table a), and I want to know which of the functions, procedures and views are using my table (table a).

Answer 1

try this

select * from dba_source where text like '%:tablename%'

Answer 2

You can check in DBA_DEPENDENCIES table using below query:

  select * from DBA_DEPENDENCIES where REFERENCED_NAME ='tableA' --YOUR TABLE NAME;

QUICK CHECK:

    create table TEST (id number(5), name varchar2(50) ); 
     --Table created

    insert into TEST values(1,'mahi');
     --1 row created. 

    commit;                                                                                                               
     --Commit complete.   

    create or replace procedure PROC_TEST As
    v_name varchar2(50);
    BEGIN
    select name into v_name from TEST where id=1;
    dbms_output.put_line('o/p : ' || ' ' || v_name);
    END;
    /
       --Procedure created. 

      Exec PROC_TEST();                                                                                                                       
      --o/p :  mahi                                                                                                                 

      --PL/SQL procedure successfully completed. 

QUERY:

 select * from DBA_DEPENDENCIES where REFERENCED_NAME = 'TEST';

Answer 3

i thing bellow query by "davegreen100" will work but need to place upper at both side so that it will work for upper as well as lower case.

select * from dba_source where upper(text) like upper('%:tablename%')