SYS.COMMENTS on ORACLE

On SQL SERVER it was kinda easy to find any object that referenced a table, column, etc... all you needed to do was to query sys.comments, and there it was. To find any reference to a "xpto" object, you could:

SELECT * 
FROM   syscomments c 
       inner join sysobjects o 
               ON c.id = o.id 
WHERE  c.text LIKE '%xpto%' 

On more recent versions of SQL SERVER, syscomments is no more, so the alternative is OBJECT_DEFINITION function:

SELECT * 
FROM   sys.procedures 
WHERE  Object_definition(object_id) LIKE '%xpto%' 

On ORACLE, you can achieve the same result using ALL_SOURCE. So if you need to finf any view, function, procedure, etc that references a table or column named "xpto", you can do this:

SELECT * 

FROM   all_source 

WHERE  text LIKE '%xpto%' 

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06