TABLE FINDER

Table_Finder is a procedure to find all the tables that are queried in a particular package or procedure. It takes Package name or procedure name as input parameter. Final output will be the list of table names that are queried by that package along with the table?s owner name. It finds the table names involved in all schemas. This developed procedure will be useful to all development and enhancement projects as it is gives output without consuming time.

CREATE OR REPLACE PROCEDURE table_finder(pkg_name IN VARCHAR) AS
CURSOR Test IS
 SELECT owner,referenced_name,object_type FROM user_dependencies ud,all_objects ao,user_synonyms us
  WHERE ud.name=pkg_name
  AND ud.referenced_type IN ( 'TABLE','SYNONYM')
  AND ud.referenced_owner NOT IN ('SYS','PUBLIC')
  AND ud.referenced_name=ao.object_name
  AND ud.referenced_name=us.synonym_name(+)
  AND ao.object_type LIKE'TABLE';
   
     BEGIN
           for rec in test
               loop
                      dbms_output.put_line(rec.owner);
                      dbms_output.put_line(rec.referenced_name);
                      dbms_output.put_line(rec.object_type);
               end loop;
      END;