오라클 계정에 있는 모든 컬럼의 값을 검색하여 특정 값 찾기

사이트를 관리하다 보면 오라클 계정에 있는 특정 값을 검색해야 되는 경우가 있습니다. 예를 들어서 개인정보 보호법 때문에 주민번호를 지워야 되는 경우가 되겠지요.

 

그런데, 관리하는 사이트가 커서 컬럼이름을 알고는 있는데 어느 테이블에 값이 있는지 확인할 수가 없다면 일일이 하나씩 살펴보면서 확인을 해야 되는데 아래 쿼리는 한 번에 모든 칼럼의 값을 검색하여 특정 값을 찾는 방법입니다.

 

 

 

 

인터넷 뒤지다가 발견한건데 쿼리를 조금 수정해서 사용하려고 했는데, 일이 복잡해질 것 같아서 일단 보류하고 있는 중입니다.

 

왜냐하면 일단 칼럼 이름으로 검색을 한 뒤 그 칼럼 이름으로 검색한 값들을 다시 조회해야 하기 때문인데, 펑션이나 함수로 만들어서 처리하기에는 초보자분들이 따라 하기 힘들 것 같아서 글 쓰는 의미가 없어지지 안 나는 생각에 그냥 이렇게 쓰는 게 일단은 나을 것 같습니다.

 

쿼리샘플 안내

 

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 '%찾을내용%'
ORDER BY TABLE_NAME,COLUMN_ID;

 

 

 

 

 

 

 

 

위의 쿼리로 조회를 하면 아래와 같은 값이 나오는데요.

 

SELECT count(칼럼 이름 1) cnt, '테이블.컬럼이름1' target, TO_CHAR(컬럼이름1) contents FROM 컬럼 WHERE TO_CHAR(컬럼이름1) like '%찾을내용%'
GROUP BY 컬럼이름1
UNION ALL

SELECT count(칼럼 이름 2) cnt, '테이블.컬럼이름2' target, TO_CHAR(컬럼이름2) contents FROM 컬럼 WHERE TO_CHAR(컬럼이름1) like '%찾을내용%'
GROUP BY 컬럼이름2
UNION ALL

 

해당되는 내용이 있는 테이블을 조회할 수 있게 만든 쿼리를 출력하게 만들어져 있습니다. 그래서 마지막 유니온을 제거하고 쿼리 실행하면 해당하는 자료가 있는 모든 칼럼을 검색해주시면 됩니다.

 

조회내용 예제 안내

 

조회내용 예제는 아래와 같습니다.

 

CNT (카운트)	TARGET(테이블,컬럼)	CONTENTS(내용)
1	TEST1.FIRTST_SEQ	1000
1	TEST1.SECOND_SEQ	10001
2	TEST2.OTHER_SEQ	10002
4	TEST3.ETC_SEQ	10005

 

여기까지 오라클 계정에 있는 칼럼의 값을 검색해서 특정 값을 찾는 방법을 살펴보았습니다. 인터넷에서 이렇게 조회하는 쿼리를 보고 유용하다 싶어서 저장 해 놓았었는데, 원 출처를 현재 알 수가 없네요. 유용한 정보가 되시길 바랍니다.