DECLARE
v_search_terms VARCHAR2(4000) := 'word1,word2,word3'; -- Замените на свои слова через запятую
v_sql VARCHAR2(4000);
v_count NUMBER;
v_result SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('Таблица|Столбец|Найденное слово|Количество совпадений');
FOR c IN (
SELECT table_name, column_name
FROM user_tab_columns
WHERE data_type IN ('VARCHAR2', 'CHAR', 'CLOB', 'VARCHAR', 'NVARCHAR2', 'NCHAR', 'NCLOB')
) LOOP
FOR term IN (
SELECT TRIM(REGEXP_SUBSTR(v_search_terms, '[^,]+', 1, LEVEL)) AS search_term
FROM dual
CONNECT BY REGEXP_SUBSTR(v_search_terms, '[^,]+', 1, LEVEL) IS NOT NULL
) LOOP
BEGIN
v_sql := 'SELECT COUNT(*) FROM (SELECT 1 FROM ' || c.table_name ||
' WHERE ' || c.column_name || ' LIKE ''%' || term.search_term || '%'' AND ROWNUM <= 100)';
EXECUTE IMMEDIATE v_sql INTO v_count;
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE(c.table_name || '|' || c.column_name || '|' || term.search_term || '|' || v_count);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL; -- Пропускаем ошибки (например, если нельзя преобразовать тип)
END;
END LOOP;
END LOOP;
END;
/
SET SERVEROUTPUT ON
DECLARE
-- список слов для поиска
TYPE t_words IS TABLE OF VARCHAR2(100);
v_words t_words := t_words('apple', 'orange', 'banana');
v_sql VARCHAR2(4000);
v_count NUMBER;
BEGIN
FOR rec IN (
SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR', 'CLOB', 'NCLOB')
AND owner NOT IN ('SYS', 'SYSTEM')
) LOOP
-- формируем условие поиска по списку слов
v_sql := 'SELECT COUNT(*) FROM (SELECT ' || rec.column_name ||
' FROM ' || rec.owner || '.' || rec.table_name ||
' WHERE ROWNUM <= 100) WHERE ';
FOR i IN 1 .. v_words.COUNT LOOP
IF i > 1 THEN
v_sql := v_sql || ' OR ';
END IF;
v_sql := v_sql || rec.column_name || ' LIKE ''%' || v_words(i) || '%''';
END LOOP;
-- выполнить запрос и вывести результат, если найдено совпадение
EXECUTE IMMEDIATE v_sql INTO v_count;
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE(rec.owner || '.' || rec.table_name || '.' || rec.column_name || ' (' || v_count || ')');
END IF;
END LOOP;
END;
/