Как найти нужные столбцы в таблице oracle по содержанию?

Question

[Niptlox Company]

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

Comments

[Niptlox Company]

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

[Niptlox Company]

DECLARE
  v_search_terms VARCHAR2(4000) := 'word1,word2,word3'; -- Замените на свои слова через запятую
  v_sql VARCHAR2(4000);
  v_count NUMBER;
  v_total_columns NUMBER := 0;
  v_processed_columns NUMBER := 0;
BEGIN
  -- Подсчет общего количества столбцов для прогресса
  SELECT COUNT(*) INTO v_total_columns
  FROM user_tab_columns 
  WHERE data_type IN ('VARCHAR2', 'CHAR', 'CLOB', 'VARCHAR', 'NVARCHAR2', 'NCHAR', 'NCLOB');
  
  DBMS_OUTPUT.PUT_LINE('=== Начало поиска. Всего столбцов для проверки: ' || v_total_columns || ' ===');
  DBMS_OUTPUT.PUT_LINE('Формат вывода: [Прогресс] Таблица.Столбец | Статус');
  DBMS_OUTPUT.PUT_LINE('Результаты:');
  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')
    ORDER BY table_name, column_name
  ) LOOP
    v_processed_columns := v_processed_columns + 1;
    DBMS_OUTPUT.PUT_LINE('[' || v_processed_columns || '/' || v_total_columns || '] ' || 
                         c.table_name || '.' || c.column_name || ' | Проверка...');
    
    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
          DBMS_OUTPUT.PUT_LINE('[' || v_processed_columns || '/' || v_total_columns || '] ' || 
                               c.table_name || '.' || c.column_name || ' | Ошибка: ' || SQLERRM);
      END;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('[' || v_processed_columns || '/' || v_total_columns || '] ' || 
                         c.table_name || '.' || c.column_name || ' | Проверка завершена');
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('=== Поиск завершен. Проверено ' || v_processed_columns || ' столбцов ===');
END;
/

Answer 1

[Niptlox Company]

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