Oracle 在所有表的所有列中搜索字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6389666/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Search all tables all columns for string
提问by Jim
I need to search our oracle database for a string in all tables and columns. I have the below query I found online but when I execute it I get the following error
我需要在我们的 oracle 数据库中搜索所有表和列中的字符串。我在网上找到了以下查询，但是当我执行它时出现以下错误
Any help is appreciated
任何帮助表示赞赏
ORA-06550: line 6, column 31:
PL/SQL: ORA-00904: "COLUMN_NAME": invalid identifier
ORA-06550: line 6, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 30:
PLS-00364: loop index variable 'T' use is invalid
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored
ORA-06550: line 12, column 38:
PLS-00364: loop index variable 'T' use is invalid
ORA-06550: line 12, column 16:
PL/SQL: Statement ignored
BEGIN
FOR t IN (SELECT table_name, column_name FROM all_tables) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||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.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
回答by Justin Cave
At a minimum, you need to query ALL_TAB_COLUMNS, not ALL_TABLES
至少，您需要查询 ALL_TAB_COLUMNS，而不是 ALL_TABLES
DECLARE
match_count integer;
v_search_string varchar2(4000) := <<string you want to search for>>;
BEGIN
FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns) LOOP
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;
END LOOP;
END;
/
If you are looking for a string, however, you would almost certainly want to restrict yourself to looking for columns that could store a string. It wouldn't make sense, for example, to search a DATE column for a string. And unless you have a great deal of a priori knowledge about what a BLOB column contains and the ability to parse the BLOB column's binary formatting, it wouldn't make sense to search a BLOB column for a string. Given that, I suspect you want something more like
但是，如果您正在寻找一个字符串，您几乎肯定希望限制自己寻找可以存储字符串的列。例如，在 DATE 列中搜索字符串是没有意义的。除非您对 BLOB 列包含的内容以及解析 BLOB 列的二进制格式的能力有大量先验知识，否则在 BLOB 列中搜索字符串是没有意义的。鉴于此，我怀疑你想要更像
DECLARE
match_count integer;
v_search_string varchar2(4000) := <<string you want to search for>>;
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;
/
Of course, this is going to be insanely slow-- you'd full scan every table once for every string column in the table. With moderately large tables and a moderate number of string columns, that is likely to take quite a while.
当然，这会非常慢——您需要为表中的每个字符串列对每个表进行一次全面扫描。对于中等大的表和中等数量的字符串列，这可能需要很长时间。
