Oracle: Searching for a keyword in the database objects
It can be vitally important to know if an object is being referenced in a stored procedure or function, and although TOAD very helpfully will tell you all dependencies of an object, it is possible (if you are working on a horrible legacy project like me), to have SQL created on the fly and executed as a string. Yech!
SQL for searching your oracle procedures and functions:
SELECT *
FROM USER_SOURCE
WHERE LOWER(TEXT) LIKE LOWER('%to_date%')
UPDATE:I’ve also decided to include some code to search tables that contain a keyword. This can be very useful if you have a column name but don’t know table it’s contained in
SELECT *
FROM all_tab_columns
WHERE LOWER(column_name) LIKE LOWER('%SearchTerm%');
