CODEWITHSUNDEEP
sqloracle-databaseplsql
Robbin Rod
Robbin Rod

Reputation: 19

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

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).

Upvotes: 1

Views: 481

Answers (3)

vishnu sable
vishnu sable

Reputation: 358

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%')

Upvotes: 0

mahi_0707
mahi_0707

Reputation: 1062

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';

QUERY OUTPUT

Upvotes: 1

davegreen100
davegreen100

Reputation: 2115

try this

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

Upvotes: 1

Related Questions