오라클 내의 전체 테이블 또는 컬럼을 조회

 

출처: https://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle

--it run completed -- no error

    SET SERVEROUTPUT ON SIZE 100000

DECLARE
   v_match_count     INTEGER;
   v_counter         INTEGER;




v_owner           VARCHAR2 (255) := 'VASOA';
v_search_string   VARCHAR2 (4000) := '99999';
v_data_type       VARCHAR2 (255) := 'CHAR';
v_sql             CLOB := '';

BEGIN
   FOR cur_tables
      IN (  SELECT owner, table_name
              FROM all_tables
             WHERE     owner = v_owner
                   AND table_name IN (SELECT table_name
                                        FROM all_tab_columns
                                       WHERE     owner = all_tables.owner
                                             AND data_type LIKE
                                                       '%'
                                                    || UPPER (v_data_type)
                                                    || '%')
          ORDER BY table_name)
   LOOP
      v_counter := 0;
      v_sql := '';

      FOR cur_columns
         IN (SELECT column_name, table_name
               FROM all_tab_columns
              WHERE     owner = v_owner
                    AND table_name = cur_tables.table_name
                    AND data_type LIKE '%' || UPPER (v_data_type) || '%')
      LOOP
         IF v_counter > 0
         THEN
            v_sql := v_sql || ' or ';
         END IF;

         IF cur_columns.column_name is not null
         THEN
            v_sql :=
                  v_sql
               || 'upper('
               || cur_columns.column_name
               || ') ='''
               || UPPER (v_search_string)||'''';

            v_counter := v_counter + 1;
         END IF;

      END LOOP;

      IF v_sql is  null
      THEN
         v_sql :=
               'select count(*) from '
            || v_owner
            || '.'
            || cur_tables.table_name;

      END IF;

      IF v_sql is not null
      THEN
         v_sql :=
               'select count(*) from '
            || v_owner
            || '.'
            || cur_tables.table_name
            || ' where '
            || v_sql;
      END IF;

      --v_sql := 'select count(*) from ' ||v_owner||'.'|| cur_tables.table_name ||' where '||  v_sql;


      --dbms_output.put_line(v_sql);
      --DBMS_OUTPUT.put_line (v_sql);

      EXECUTE IMMEDIATE v_sql INTO v_match_count;

      IF v_match_count > 0
      THEN
        DBMS_OUTPUT.put_line (v_sql);
        dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
      END IF;

   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Error when executing the following: '
         || DBMS_LOB.SUBSTR (v_sql, 32600));
END;
/

 

 

Modifying the code to search case-insensitively using a LIKE query instead of finding exact matches...

DECLARE
  match_count INTEGER;
  -- Type the owner of the tables you want to search.
  v_owner VARCHAR2(255) :='USER';
  -- Type the data type you're looking for (in CAPS). Examples include: VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2(255) :='VARCHAR2';
  -- Type the string you are looking for.
  v_search_string VARCHAR2(4000) :='Test';
BEGIN
  dbms_output.put_line( 'Starting the search...' );
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP
    EXECUTE IMMEDIATE 
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE LOWER('||t.column_name||') LIKE :1'
    INTO match_count
    USING LOWER('%'||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;

출처: https://intro0517.tistory.com/157

오라클 내의 전체 테이블 또는 컬럼을 조회하려면 SYS내의 VIEW를 이용하면됩니다.

 

1. ALL_TABLES

- 로그인 된 계정의 권한으로 접근할 수 있는 모든 테이블들

- 예 ) 테이블명에 "테스트"를 포함한 테이블 검색 : 

        SELECT * FROM ALL_TABLES WHERE LIKE '%테스트%';

 

2. USER_TABLES

- 로그인 된 계정이 소유하고 있는 테이블들 

  SELECT * FROM ALL_TABLES WHERE OWNER = '로그인된계정' 과 같다.

 

3. ALL_TAB_COLUMNS

- 로그인 된 계정의 권한으로 접근할 수 있는 모든 테이블 내의 컬럼들

- 예 ) 컬럼명에 "테스트"를 포함한 컬럼 검색

        SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%테스트%'

 

4. USER_TAB_COLUMNS

- 로그인된 계정이 소유하고 있는 테이블들

  SELECT * FROM ALL_TAB_COLUMNS WHERE OWNER = '로그인된계정' 과 같다.

 

 

 

위 테이블들을 이용해서 여러 응용이 가능합니다.

그런데..... 속도는 느려요...ㅋㅋ
그대로 일일이 노가다 해서 찾는거 보다는 좋겠죠?

 

 

응용 - 1 ) 데이터베이스 전체 데이터 수 구하기 - 아래의 쿼리로 조회된 값들을 이어서 쿼리문을 만들 수 있습니다.

 

[쿼리문 만들기 위한 조회]

 

SELECT 'SELECT (' FROM DUAL UNION ALL

SELECT '(SELECT count(*) cnt  FROM '||TABLE_NAME||') +  '  FROM USER_TABLES UNION ALL

SELECT '0) TOTAL_COUNT FROM DUAL; ' FROM DUAL;

 

[조회된 결과값으로 쿼리만들기]

 

SELECT (

(SELECT count(*) cnt  FROM T_CNKC_INTG_PRSN_INFO01L1_0329) +  

(SELECT count(*) cnt  FROM T_CNKC_INTG_DPRT01M1_B) +  

...

0) TOTAL_COUNT FROM DUAL; 

 

 

응용 - 2 ) 데이터베이스 전체 컬럼에서 원하는 데이터 검색 - 아래의 쿼리로 조회된 값들을 이어서 쿼리문을 만들 수 있습니다.

 

[쿼리문 만들기 위한 조회]

 

SELECT 'SELECT count('||COLUMN_NAME||') cnt, '''||TABLE_NAME||'.'||COLUMN_NAME||''' target, TO_CHAR('||COLUMN_NAME||') contents FROM '||TABLE_NAME||' WHERE TO_CHAR('||COLUMN_NAME||') like ''%'||'찾을내용'||'%'' 

GROUP BY '||COLUMN_NAME||'

UNION ALL' as query FROM USER_TAB_COLUMNS

WHERE COLUMN_NAME like '%RTUR_ID%'

ORDER BY TABLE_NAME,COLUMN_ID

 

[조회된 결과값으로 쿼리만들기]

 

SELECT count(RTUR_ID) cnt, 'T_CNKC_INTG_USER01H1.RTUR_ID' target, TO_CHAR(RTUR_ID) contents FROM T_CNKC_INTG_USER01H1 WHERE TO_CHAR(RTUR_ID) like '%찾을내용%' 

GROUP BY RTUR_ID

UNION ALL

SELECT count(RTUR_ID) cnt, 'T_CNKC_INTG_USER01H1_1114.RTUR_ID' target, TO_CHAR(RTUR_ID) contents FROM T_CNKC_INTG_USER01H1_1114 WHERE TO_CHAR(RTUR_ID) like '%찾을내용%' 

GROUP BY RTUR_ID

UNION ALL

SELECT count(RTUR_ID) cnt, 'T_CNKC_INTG_USER01M1.RTUR_ID' target, TO_CHAR(RTUR_ID) contents FROM T_CNKC_INTG_USER01M1 WHERE TO_CHAR(RTUR_ID) like '%찾을내용%' 

GROUP BY RTUR_ID

UNION ALL

...

마지막 UNION ALL 삭제