在所有列，所有表中搜索特定值 [英] Search all columns, all tables for a specific value

问题描述

我知道这是一个重复的问题，但是我找不到重新开始讨论的方法.我试图创建一个存储的过程，它将在所有表的所有列中搜索一个值.这是我到目前为止创建的:

I know this is a duplicate question, but I couldn't find a way to reopen the discussion. Im trying to create a stored proc that will search all columns in all tables for a value. This is what I created so far:

CLEAR SCREEN
SET VERIFY OFF
ACCEPT val CHAR PROMPT 'What value do you want to search for: '
CLEAR SCREEN;
DECLARE
 match_count integer;
 v_search_string varchar2(4000) := <<val>>;
 BEGIN  
 FOR t IN (SELECT owner,
 table_name, 
 column_name 
 FROM all_tab_columns
 WHERE data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2', 
 'CLOB', 'NCLOB') ) 
 LOOP   
   BEGIN
     EXECUTE IMMEDIATE    
       'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
       ' WHERE '||t.column_name||' = :1'   
       INTO match_count  
       USING v_search_string; 
     IF match_count > 0 THEN 
        dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
     END IF; 

    EXCEPTION
      WHEN others THEN
        dbms_output.put_line( 'Error encountered trying to read ' ||
            t.column_name || ' from ' || 
            t.owner || '.' || t.table_name );
     END;
 END LOOP;
END;
/

但是我得到了错误.任何帮助将不胜感激！

but I get errors. Any help would be highly appreciated!!

推荐答案

查看评论:

CLEAR SCREEN
SET VERIFY OFF
ACCEPT val CHAR PROMPT 'What value do you want to search for: '
CLEAR SCREEN;

DECLARE
    match_count                             INTEGER;
    v_search_string                         VARCHAR2(4000) := '&val'; /* this was <<val>> */
BEGIN
    FOR t IN (SELECT owner,
                     table_name,
                     column_name
                FROM all_tab_columns
               WHERE data_type IN ('CHAR',
                                   'VARCHAR2',
                                   'NCHAR',
                                   'NVARCHAR2',
                                   'CLOB',
                                   'NCLOB')) 
    LOOP
        BEGIN
            EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name || ' WHERE ' || t.column_name || ' = :1' INTO match_count USING v_search_string;

            IF match_count > 0
            THEN
                DBMS_OUTPUT.put_line(t.owner || '.' || t.table_name || '     ' || t.column_name || ' ' || match_count);
            END IF;
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line('Error encountered trying to read ' || t.column_name || ' from ' || t.owner || '.' || t.table_name);
        END;
    END LOOP; 
END;
/     

这篇关于在所有列，所有表中搜索特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！