[Oracle-SQL] 특정 컬럼명을 소유한 모든 Table 조회

오라클 데이터 베이스로 프로젝트나 운영 업무 中
'특정 컬럼의 값을 전부 또는 일부를 변경해야 하는 요구 사항이 있는 경우'
특정 컬럼명을 가진 Table을 모두 조사해야 할 때가 있습니다.
이런 상황에서는 모든 컬럼정보를 담고 있는 DBA_TAB_COLUMNS 를 이용하시면 됩니다.

DBA_TAB_COLUMNS

 

DBA_TAB_COLUMNS는 데이터베이스에 있는 Clusters, Tables, Views의 모든 컬럼 정보를 담고 있습니다.

DBA_TAB_COLUMNS View의 중요 컬럼 정보는 아래와 같습니다.

특정 컬럼을 소유한 모든 Table 조회

 

예제에 사용하는 EMP, DEPT 테이블 스크립트는 아래를 참고 하세요.

오라클 EMP, DEPT 테이블 데이터 생성하기

테스트를 위하여 EMP Table을 이용하여 EMP1 ~ EMP5까지 복제

 

-- EMP Table을 이용하여 EMP1 ~ 5까지 복제
CREATE TABLE EMP1 AS SELECT * FROM EMP;
CREATE TABLE EMP2 AS SELECT * FROM EMP;
CREATE TABLE EMP3 AS SELECT * FROM EMP;
CREATE TABLE EMP4 AS SELECT * FROM EMP;
CREATE TABLE EMP5 AS SELECT * FROM EMP;

 

Table 생성 확인

 

SELECT *
  FROM DBA_TABLES
 WHERE TABLE_NAME LIKE 'EMP%'
   AND OWNER = 'TEST'	-- OWNER(소유자)
;

'EMPNO' 컬럼을 가진 모든 Table 조회

 

-- 'EMPNO' 컬럼을 소유한 Table 조회
SELECT *
  FROM DBA_TAB_COLUMNS
 WHERE COLUMN_NAME = 'EMPNO'  -- 조회할 컬럼명
   AND OWNER = 'TEST';

응용 - 스크립트문 생성

 

사번[empno]이 <'7900'>인 JAMES의 job을 'CLERK' --> 'SALESMAN'으로 모든 Table을 일괄 변경하는
스크립트(Script)문을 생성하는 방법입니다.

 

생성 해야할 수정 스크립트

-- 사번(empno)이 7900인 사원의 직업을 'SALESMAN'으로 변경
UPDATE EMP
   SET JOB = 'SALESMAN'
 WHERE EMPNO = '7900'

1. EMP --> dba_tab_columns.tables_name으로 대체 한다.
2. ' --> '' 으로 변경
   ex)'SALESMAN' --> ''SALESMAN'

SELECT 
	' UPDATE '|| X.TABLE_NAME ||
	'    SET JOB = ''SALESMAN'''||
--	'  WHERE EMPNO = ''7900'';'     
	'  WHERE '||X.COLUMN_NAME||' = ''7900'';' 
	AS SCRIPT
  FROM DBA_TAB_COLUMNS X
 WHERE COLUMN_NAME = 'EMPNO'
   AND OWNER = 'TEST';