[Oracle]오라클에서 특정 컬럼이 포함된 테이블 찾기

오늘은 오라클에서 컬럼명으로 테이블을 찾는 방법을

정리해보고자 합니다.

DB를 쓰다보면은 여러 테이블들에 공통적으로 

같은 컬럼명을 쓰는 경우가 많이 발생이 됩니다.

그런데 그러다보면 해당 컬럼이 무슨무슨 테이블에 들어가져있는지

기억이 안나기도 합니다.

 

이럴 때 해당 컬럼을 포함한 Table 리스트를 찾아볼 수 있습니다.

오늘은 이러한 방법을 정리해보고자 합니다.

 

반응형

 

1. all_col_comments

우선 첫번째 방법으로는 코멘트가 저장된 테이블을 기준으로

찾을 수 있습니다.

오라클에서는 테이블별, 컬럼별 코맨트 정보를 저장하고 있습니다.

해당 테이블?뷰?에서 특정 컬럼이 들어간 테이블을 찾는 것입니다.

 

오라클에서 컬럼별 코멘트 정보가 들어가져 있는 

시스템 테이블은 all_col_comments 입니다.

그래서 해당 테이블을 이용하여 찾을 수 있습니다.

 

all_col_comments 테이블을 이용하여 찾기 위한 쿼리는

다음과 같습니다.

SELECT *
  FROM all_col_comments
 WHERE column_name = 'AGE' --컬럼명 대문자!!!
;

 

위처럼 해주시면 되십니다.

column_name에 찾고자 하는 컬럼명을 입력해주시면 되십니다.

위 쿼리에서도 주석으로 추가는 해놓았는데,

컬럼명은 꼭 대문자로 해주셔야 하십니다.

 

 

 

 

저는 위처럼 쿼리를 작성하였습니다.

코맨트 정보가 들어간 테이블에서 AGE라는 컬럼명을 사용하는

모든 테이블을 찾는 것입니다.

 

 

위처럼 쿼리를 작성하고 실행을 하게 되면

결과는 아래처럼 나오게 됩니다.

 

 

위처럼 나오는 것을 확인할 수 있습니다.

위 이미지를 보면 코맨트로는 나이라고 한 곳도 있고,

연령이라고 입력한 곳도 있고,

코멘트를 입력하지 않은 곳들도 있네요.

 

오너명이나 테이블명은 숨겨놓았습니다.

 

 

 

728x90

 

2. all_tab_columns

이번에는 컬럼의 데이터 타입 등이 들어간 

시스템 테이블에서 데이터를 찾아보도록 하겠습니다.

데이터타입등이 들어간 시스템 테이블의 이름은 

all_tab_columns 입니다.

해당 테이블에서 데이터를 조회를 해보도록 하겠습니다.

 

 

all_tab_columns 테이블의 경우에는 

코멘트등은 없지만, 데이터 타입이나 길이 등이 들어있어서

1번과 다른 의미의 데이터를 가지고 있습니다.

그래서 이곳에서도 찾을 수 있습니다.

 

 

all_tab_columns 테이블에서 특정 컬럼이 들어간

Table을 찾는 방법은 다음과 같습니다.

 

SELECT *
  FROM all_tab_columns
 WHERE column_name = 'AGE' --컬럼명 대문자
;

 

위처럼 해주시면 되십니다.

위 1번과 마찬가지로 테이블명은 대문자로 입력을 해주시면 되십니다.

그러면 이번에는 컬럼기본정보가 들어가있는 

시스템 테이블에서 조회를 하게 됩니다.

 

 

 

 

저는 위처럼 쿼리를 작성하였습니다.

그리고 실행을 하게 되면 결과는 아래처럼 나오게 되었습니다.

 

 

위처럼 나오는 것을 확인할 수 있습니다.

뒤쪽에도 더 있는데 옆으로 길다보니 캡처에서는 짤렸네요.

문자형의 경우에는 DATA_LENGTH가 문자열의 길이입니다.

그리고 숫자형의 경우에는 DATA_PRECISION이 정수부분이고,

DATA_SCALE의 값이 소수점 이하 아래 부분입니다.

이렇게 별도로 들어가져있네요.

 

 

SMALL

 

 

3. 데이터타입, 코멘트 같이 조회하기

이번에는 데이터타입, 코멘트 등을

같이 조회하는 방법에 대해서 정리를 해보고자 합니다.

저같은 경우에는 보통 필요한 것이 많이 필요하다고 해도

OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COMMENTS 정도입니다.

그 외에 다른 것들은 그다지 많이 보지는 않고 있습니다.

그래서 이번에는 이러한 것들을 한번에 조회하는 방법에 대해서

정리를 해보고자 합니다.

 

 

위 1번과 2번의 테이블을 JOIN 해서

필요한 정보들만 나오도록 한 것입니다.

쿼리는 다음과 같습니다.

 

SELECT a.owner
, a.table_name
, a.column_name
, a.data_type ||
CASE WHEN a.data_type LIKE '%CHAR%'THEN '(' || a.data_length || ')'
     WHEN a.data_type = 'NUMBER' AND a.data_precision > 0 AND a.data_scale > 0 THEN 
     
'(' || a.data_precision || ',' || a.data_scale || ')'
     WHEN a.data_type = 'NUMBER' AND A.data_precision > 0 THEN '(' || a.data_precision || ')'
     
END AS data_type
, b.comments       
FROM all_tab_columns a
JOIN  all_col_comments b ON a.owner = b.owner AND a.table_name = b.table_name AND a.column_name = b.column_name
WHERE a.column_name = 'AGE' --컬럼명 대문자
;

 

쿼리를 위처럼 작성하였습니다.

위 쿼리에서는 제가 위에서 얘기한

오너, 테이블명, 컬럼명, 데이터타입, 길이, 코맨트 정도만 나오도록 한 것입니다.

 

 

그리고 2번에서 얘기한 것처럼 문자열일경우와

숫자형일 경우에 따라서, 소수점 이하자리가 있냐 없냐에 따라서

보기 좋게 표현하도록 해놓은 것입니다.

 

 

 

 

위처럼 쿼리를 작성하였습니다.

이 쿼리에서도 여전히

컬럼명은 대문자로 입력을 해주시면 되십니다.

 

 

 

위처럼 쿼리를 작성하고 실행을 하게되면

결과는 아래처럼 나오게 됩니다.

 

 

위처럼 나오는 것을 확인할 수 있습니다.

제가 위에서 얘기한 것처럼 오너, 테이블명, 컬럼명, 데이터타입, 길이, 코맨트 정도만

딱 나오도록 한 것입니다.

 

 

 

 

 

오늘은 오라클에서 특정 컬럼이 들어간 테이블 리스트를 조회하는 방법에 대해서

정리를 해보았습니다.

특정 컬럼이 들어간 테이블을 찾는 것은 저도 가끔 필요하여

가끔 쓰는 방법입니다.

 

 

나중에 사용하는 테이블이 많으면

무슨 테이블에서 이 컬럼이 있었는지 기억을 할 수가 없기 때문입니다.

그래서 저도 쓰고 있는 것이니

필요하신 분들은 사용을 하시면 좋을 것 같습니다.

 

 

 

이상으로 Oracle에서 특정 Column 을 사용중인 Table을 찾는 방법이였습니다~

 

 

 

 

 

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다