I wrote some code that allows you to find exactly where all references in scripts (sprocs, views) your objects are. This includes tables, views, indexes, sequences. Run it, and let me know what you think!
WITH OBJECT_OWNERS AS ( SELECT 'SYS' AS OBJECT_OWNER FROM DUAL ) SELECT OBJECT_OWNER, OBJECT_TYPE, OBJECT_NAME, REFERENCED_IN_OWNER, REFERENCED_IN_OBJECT_TYPE, REFERENCED_IN_OBJECT, REFERENCED_IN_LINE, REFERENCED_IN_TEXT FROM ( SELECT OBJECT_TYPE, OBJECT_NAME, OWNER AS OBJECT_OWNER FROM ALL_OBJECTS WHERE OWNER IN ( SELECT OBJECT_OWNER FROM OBJECT_OWNERS ) MINUS SELECT 'TABLE' AS OBJECT_TYPE, OBJECT_NAME, OWNER AS OBJECT_OWNER FROM ALL_OBJECTS WHERE OWNER IN ( SELECT OBJECT_OWNER FROM OBJECT_OWNERS ) AND OBJECT_TYPE = 'MATERIALIZED VIEW' ) QUALIFIER LEFT JOIN ( SELECT OWNER AS REFERENCED_IN_OWNER, TYPE AS REFERENCED_IN_OBJECT_TYPE, NAME AS REFERENCED_IN_OBJECT, LINE AS REFERENCED_IN_LINE, TEXT AS REFERENCED_IN_TEXT FROM ALL_SOURCE WHERE TEXT NOT LIKE '--%' ) DETAILS ON INSTR(DETAILS.REFERENCED_IN_TEXT, QUALIFIER.OBJECT_NAME) != 0 ORDER BY OBJECT_OWNER, OBJECT_TYPE, OBJECT_NAME, REFERENCED_IN_OWNER, REFERENCED_IN_OBJECT_TYPE, REFERENCED_IN_OBJECT, REFERENCED_IN_LINE
Reblogged this on Dinesh Ram Kali..
LikeLike