在所有列，所有表中搜索特定值

本文介绍了在所有列，所有表中搜索特定值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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; /