ORACLE - 한글 데이터가 포함된 COLUMN, TABLE 조회 (by owner)

 

글은 추후에 다시 다듬을 예정.

 

 

1. 프로시저 생성 (DATA_TYPE 조건문에 데이터 유형 적용)

create or replace PROCEDURE SHOW_KR_COLUMN
    (
        OWNER_PARAM IN VARCHAR2
    )
    IS

    CURSOR TABLE_COLUMN IS
        SELECT
            OWNER,
            TABLE_NAME,
            COLUMN_NAME
        FROM ALL_TAB_COLUMNS
        WHERE OWNER = OWNER_PARAM
        AND (DATA_TYPE = 'VARCHAR2' OR DATA_TYPE = 'NVARCHAR2')
        ORDER BY OWNER ASC;

BEGIN
    FOR VAR IN TABLE_COLUMN LOOP
        EXECUTE IMMEDIATE 'INSERT INTO TB_SHOW_KR_COLUMN(KR_CNT, TABLE_NAME, COLUMN_NAME, OWNER) ' || 'SELECT A.KR_CNT, A.TABLE_NAME, A.COLUMN_NAME, A.OWNER FROM (SELECT COUNT(*) AS KR_CNT, '''||VAR.TABLE_NAME||''' AS TABLE_NAME, '''||VAR.COLUMN_NAME||''' AS COLUMN_NAME, '''||VAR.OWNER||''' AS OWNER FROM '||VAR.TABLE_NAME||' WHERE REGEXP_LIKE('||VAR.COLUMN_NAME||', ''[가-힝]'') AND ROWNUM = 1) A WHERE A.KR_CNT > 0';
        COMMIT;
    END LOOP;
END SHOW_KR_COLUMN;
/

 

2. 테이블 생성

create table TB_SHOW_KR_COLUMN
(
	KR_CNT NUMBER,
	TABLE_NAME VARCHAR2(256),
	COLUMN_NAME VARCHAR2(256),
	OWNER VARCHAR2(256)
)
/

 

3. 실행

 

call YTH_ACT.SHOW_KR_COLUMN('OWNER_NAME');

 

 

4. 결과