Query to search all packages for table and/or column

Is there a query I can run to search all packages to see if a particular table and/or column is used in the package? There are too many packages to open 开发者_如何学编程each one and do a find on the value(s) I'm looking for.

---

You can do this:

select *
from user_source
where upper(text) like upper('%SOMETEXT%');

Alternatively, SQL Developer has a built-in report to do this under:

View > Reports > Data Dictionary Reports > PLSQL > Search Source Code

The 11G docs for USER_SOURCE are here

---

you can use the views *_DEPENDENCIES, for example:

SELECT owner, NAME
  FROM dba_dependencies
 WHERE referenced_owner = :table_owner
   AND referenced_name = :table_name
   AND TYPE IN ('PACKAGE', 'PACKAGE BODY')

---

Sometimes the column you are looking for may be part of the name of many other things that you are not interested in.

For example I was recently looking for a column called "BQR", which also forms part of many other columns such as "BQR_OWNER", "PROP_BQR", etc.

So I would like to have the checkbox that word processors have to indicate "Whole words only".

Unfortunately LIKE has no such functionality, but REGEXP_LIKE can help.

SELECT *
  FROM user_source
 WHERE regexp_like(text, '(\s|\.|,|^)bqr(\s|,|$)');

This is the regular expression to find this column and exclude the other columns with "BQR" as part of the name:

(\s|\.|,|^)bqr(\s|,|$)

The regular expression matches white-space (\s), or (|) period (.), or (|) comma (,), or (|) start-of-line (^), followed by "bqr", followed by white-space, comma or end-of-line ($).

---

By the way, if you need to add other characters such as "(" or ")" because the column may be used as "UPPER(bqr)", then those options can be added to the lists of before and after characters.

(\s|\(|\.|,|^)bqr(\s|,|\)|$)