oracle 查询以搜索表和/或列的所有包
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4852993/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to search all packages for table and/or column
提问by Matt M
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.
是否可以运行查询来搜索所有包以查看包中是否使用了特定表和/或列？有太多包无法打开每个包并查找我正在寻找的值。
回答by Tony Andrews
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:
或者，SQL Developer 有一个内置报告来执行此操作：
View > Reports > Data Dictionary Reports > PLSQL > Search Source Code
The 11G docs for USER_SOURCE are here
USER_SOURCE 的 11G 文档在这里
回答by Vincent Malgrat
you can use the views *_DEPENDENCIES, for example:
您可以使用视图*_DEPENDENCIES，例如：
SELECT owner, NAME
FROM dba_dependencies
WHERE referenced_owner = :table_owner
AND referenced_name = :table_name
AND TYPE IN ('PACKAGE', 'PACKAGE BODY')
回答by S Meyer
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.
例如，我最近在寻找一个名为“BQR”的列，它也是许多其他列的一部分，例如“BQR_OWNER”、“PROP_BQR”等。
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.
不幸的是 LIKE 没有这样的功能，但 REGEXP_LIKE 可以提供帮助。
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:
这是查找此列并排除名称中包含“BQR”的其他列的正则表达式：
(\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 ($).
正则表达式匹配空格 (\s)、或 (|) 句点 (.)、或 (|) 逗号 (,) 或 (|) 行首 (^)，后跟“bqr”，后跟通过空格、逗号或行尾 ($)。
回答by S Meyer
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.
顺便说一句，如果您需要添加其他字符，例如“（”或“）”，因为该列可能用作“UPPER（bqr）”，那么可以将这些选项添加到前后字符列表中。
(\s|\(|\.|,|^)bqr(\s|,|\)|$)
