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;