在Oracle整个架构的所有字段中搜索给定的字符串 [英] Search for a given string in all fields of an entire schema for Oracle

问题描述

我们如何在Oracle整个架构的所有字段中搜索给定的字符串?

How can we Search for a given string in all fields of an entire schema for Oracle?

在Oracle中是否有执行上述操作的SQL脚本?如果是，怎么办?

is there an sql script to do the above in Oracle? if yes, how?

我们可以通过其他任何脚本来做到这一点吗?

Can we do it by any other script?

推荐答案

" ALL_TAB_COLUMNS 是一个systable，具有所有表列的数据类型，等等.

"ALL_TAB_COLUMNS" is a systable, have all table columns data type etc.

" USER_TAB_COLUMNS "是一个systable，具有所有表列数据类型，等等(所有者是当前用户). (感谢Rene)

"USER_TAB_COLUMNS" is a systable, have all table columns data type etc (which owner is current user). (Thanks Rene)

示例:

 SET SERVEROUTPUT ON SIZE 100000 -- maybe you have a lot of table and columns

    DECLARE
      matches INTEGER;
    BEGIN
      FOR columns IN (SELECT table_name, column_name FROM user_tab_columns where data_type = 'VARCHAR2') LOOP

        EXECUTE IMMEDIATE
          'SELECT COUNT(*) FROM '||t.table_name||' WHERE instr('||t.column_name||' , :1) > 0'
          INTO matches
          USING 'What you search';

        IF matches > 0 THEN
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||matches );
        END IF;

      END LOOP;

    END;
    /

此查询将输出table_name''column_name''并进行计数，如果您具有标准列和表名，则可以将查询更改为IF列> 0，然后在循环中编写查询UNION并返回游标，或返回表，

this query will output table_name '' column_name'' and count, if you have standart column and table names, you can change query as IF columns > 0 then write a query UNION in loop and return the cursor, or return a table,

这篇关于在Oracle整个架构的所有字段中搜索给定的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！