2

i,d like to know a way of make a query where i can find all dependencies of objects with a specific column in a specific table. I can do it with a table but i dont know how to do it with a column of that table

SELECT   type,
           name
    FROM   user_dependencies
   WHERE   referenced_name = 'table_name'
ORDER BY   type,
           name
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Unai Longvalley
  • 21
  • 1
  • 3
  • check user_source – Moudiz May 20 '19 at 13:32
  • If you're using Oracle 12.2 or later then you can get this information through PL/SCOPE. [Find out more](https://www.salvis.com/blog/2017/03/17/plscope-utils-utilities-for-plscope-in-oracle-database-12-2/) – APC May 20 '19 at 13:52

1 Answers1

1

user_dependencies and user_source dictionar views may be joined to get the pieces in which mytable's col0 exists :

SELECT d.type, d.name, s.line as line_number, s.text
  FROM user_dependencies d
  LEFT JOIN user_source s
    ON s.name = d.name
 WHERE d.referenced_name = 'MYTABLE'
   AND upper(s.text) like '%COL0%'
 ORDER BY d.type, d.name

To search through the whole DB (provided the privileges exist)

SELECT d.type, d.name, s.line as line_number, s.text
  FROM dba_dependencies d
  LEFT JOIN dba_source s
    ON s.name = d.name
 WHERE d.referenced_name = 'MYTABLE'
   AND upper(s.text) like '%COL0%'
 ORDER BY d.type, d.name

Note that : In fact mytable.* implicitly contains also your desired column.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55