如何在Oracle全表全列全行中搜索指定词汇或短语？

你之前用的SELECT * FROM all_tab_cols WHERE column_name LIKE '%WORD%'其实是在搜索列名里包含WORD的字段，而不是列值里的内容——这就是为什么没得到预期结果～要找所有表/列中存储了特定词汇的行，得用动态SQL遍历所有可能的表和字符类型列，下面是具体的实现方法：

方法：用PL/SQL块生成并执行搜索逻辑 #

这个脚本会遍历当前用户有权访问的所有表，只针对字符类型的列（比如VARCHAR2、CHAR、CLOB等）进行搜索，返回匹配的表名、列名、匹配行数，你还可以扩展它输出具体行数据：

SET SERVEROUTPUT ON;
DECLARE
    v_search_value VARCHAR2(100) := 'WORD'; -- 替换成你要搜索的词汇/短语
    v_sql VARCHAR2(4000);
    v_count NUMBER;
BEGIN
    FOR rec IN (
        SELECT owner, table_name, column_name, data_type
        FROM all_tab_cols
        WHERE data_type IN ('VARCHAR2', 'CHAR', 'CLOB', 'NCARCHAR2', 'NCHAR')
          AND owner = USER -- 只搜索当前用户的表，若要搜全库可去掉此条件（注意权限）
    ) LOOP
        -- 针对CLOB列用DBMS_LOB.INSTR，普通字符列用LIKE
        IF rec.data_type = 'CLOB' THEN
            v_sql := 'SELECT COUNT(*) FROM ' || rec.owner || '.' || rec.table_name || 
                     ' WHERE DBMS_LOB.INSTR(' || rec.column_name || ', :1) > 0';
        ELSE
            v_sql := 'SELECT COUNT(*) FROM ' || rec.owner || '.' || rec.table_name || 
                     ' WHERE ' || rec.column_name || ' LIKE ''%'' || :1 || ''%''';
        END IF;
        
        EXECUTE IMMEDIATE v_sql INTO v_count USING v_search_value;
        
        IF v_count > 0 THEN
            DBMS_OUTPUT.PUT_LINE('找到匹配：表=' || rec.owner || '.' || rec.table_name || 
                                 '，列=' || rec.column_name || '，匹配行数=' || v_count);
            -- 如果需要查看具体行数据，可取消注释以下代码（注意输出量）
            -- FOR row_rec IN (EXECUTE IMMEDIATE 'SELECT ROWID, ' || rec.column_name || ' FROM ' || rec.owner || '.' || rec.table_name || ' WHERE ' || rec.column_name || ' LIKE ''%'' || :1 || ''%''' USING v_search_value) LOOP
            --     DBMS_OUTPUT.PUT_LINE('ROWID=' || row_rec.ROWID || '，值=' || row_rec.column_value);
            -- END LOOP;
        END IF;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('处理表 ' || rec.owner || '.' || rec.table_name || ' 列 ' || rec.column_name || ' 时出错：' || SQLERRM);
END;
/

关键注意事项： #

• 权限问题：确保你有SELECT权限访问目标表，以及查询all_tables和all_tab_cols视图的权限。
• 性能优化：全库搜索会非常慢，建议：
  • 加上owner过滤，只搜索特定业务用户的表（比如owner = 'YOUR_BUSINESS_SCHEMA'）
  • 排除系统表，比如加AND table_name NOT LIKE 'SYS_%'
• CLOB列处理：CLOB类型不能直接用LIKE，所以用DBMS_LOB.INSTR来判断是否包含目标字符串。
• 输出控制：如果要查看具体行数据，记得考虑数据量——大表可能会输出海量内容，建议先看行数再针对性查询。

如果你用的是MySQL、SQL Server等其他数据库，核心逻辑类似，但系统视图和语法会有差异（比如MySQL用information_schema.columns，SQL Server用sys.tables+sys.columns）。

内容的提问来源于stack exchange，提问作者Alex Fields