CODEWITHSUNDEEP
oracle-database
Matt M
Matt M

Reputation: 3779

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.

Upvotes: 39

Views: 175442

Answers (4)

S Meyer
S Meyer

Reputation: 1

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|,|\)|$)

Upvotes: 0

S Meyer
S Meyer

Reputation: 1

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 ($).

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132580

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

Upvotes: 67

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

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')

Upvotes: 8

Related Questions