PL / SQL搜索整个数据库中的字符串 [英] PL / SQL to search a string in whole database

问题描述

不止一个问题，它的信息共享帖子。

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搜索整个数据库中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！