问题描述
不止一个问题,它的信息共享帖子。
More than a question, its an information sharing post.
我遇到了一个情况,今天我需要在应用程序的整个数据库中查找一个不知道,哪个表/列属于。
I have come across a situation today where i needed to look for a sting in the entire database of an application with no idea of, which table/column it belongs to.
下面是我写的PL / SQL块,用于帮助我的建议。希望它帮助别人满足类似的要求。
Below is a PL/SQL block i wrote and used to help my propose. Hope its helps others to with a similar requirement.
Declare
i NUMBER := 0;
counter_intable NUMBER :=0;
BEGIN
FOR rec IN (
select
'select count(*) ' ||
' from '||table_name||
' where '||column_name||' like''%732-851%'' ' as sql_command
from user_tab_columns
where data_type='VARCHAR2'
)
LOOP
execute immediate rec.sql_command into counter_intable;
IF counter_intable != 0 THEN
i := i + 1;
DBMS_OUTPUT.put_line ('Match found using command ::' || rec.sql_command);
DBMS_OUTPUT.put_line ('count ::' || counter_intable);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('total commands matched :: ' || i);
END;
在代码块中替换位于:732-851处的字符串
replace your string at the place of : 732-851 in the code block
推荐答案
为什么 PL / SQL ?您也可以在 SQL 中使用 xmlsequence
来执行相同操作。
Why PL/SQL? You could do the same in SQL using xmlsequence
.
搜索值'KING'
-
SQL> variable val varchar2(10)
SQL> exec :val := 'KING'
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
2 SUBSTR (table_name, 1, 14) "Table",
3 SUBSTR (column_name, 1, 14) "Column"
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
6 || column_name
7 || ' from '
8 || table_name
9 || ' where upper('
10 || column_name
11 || ') like upper(''%'
12 || :val
13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
14 ORDER BY "Table"
15 /
Searchword Table Column
----------- -------------- --------------
KING EMP ENAME
SQL>
您可以搜索任何数据类型值,请参阅 SQL在整个SCHEMA中的所有表格的所有COLUMNS中搜索VALUE
You could search for any data type values, please read SQL to Search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA
这篇关于PL / SQL搜索整个数据库中的字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!