How to list objects that are using another object?

Question

I have to move certain objects from a schema to another. The database is rather large and I want to make sure we test properly all affected objects.

On TOAD there is a Tab called "Used By" that show all objects that are referencing the selected object.

Is there a way to achieve a similar output with an SQL Command? I tried checking the different DBA Views but I haven't been able to find any with the information I need.

• mustaccio: See if this helps: stackoverflow.com/questions/1143728/…

Answer 1

This seems to be the query from TOAD when you click the used_by tab - you just need to replace the 'OBJECT_NAME' with whatever the name of the object you are interested in is, and change the object_type to whatever is appropriate for your object.

WITH Objs
 AS ( SELECT object_id
 FROM (SELECT object_id, referenced_object_id
 FROM public_dependency
 WHERE referenced_object_id <> object_id) pd
 CONNECT BY PRIOR object_id = referenced_object_id
 START WITH referenced_object_id = (select object_id from dba_objects where object_name = 'OBJECT_NAME' and object_type = 'TABLE'))
SELECT o.owner,
 o.object_type,
 o.object_name,
 o.object_id,
 o.status
 FROM sys.DBA_OBJECTS o, Objs
 WHERE o.Object_id = Objs.Object_id

You can view the SQL that TOAD generates by starting a second session, go into the session browser and then view the current statement to see the query your first TOAD session is running.