[ORACLE] 컬럼 정보 조회

[ORACLE] 컬럼 정보 조회

접속한 사용자에 속한 테이블의 정보를 조회할 수 있으며, 전체 owner의 정보가 필요한 경우
user_xxx로 시작하는 테이블을 all_xxx로 변경하면 볼 수 있다.

컬럼 정보 조회

SELECT 
    TABLE_NAME
    ,COLUMN_NAME    -- 컬럼 명
    ,DATA_TYPE      -- 유형
    ,DATA_LENGTH    -- 데이터 길이
    ,DATA_PRECISION -- NUMBER 전체 자릿수
    ,DATA_SCALE     -- NUMBER 소수점이하 표현 자릿수
    ,NULLABLE       -- NULL 여부
    ,COLUMN_ID      -- 컬럼 순서
    ,DATA_DEFAULT   -- 기본 값   
  FROM user_tab_columns -- 해당 계정에 속한 테이블 
   --  dba_tab_columns 전체 테이블의 경우 
 WHERE TABLE_NAME = 'EMP'   -- 조회 할 테이블 명

제약조건 조회

select 
    a.table_name        -- 테이블
    ,a.constraint_name  -- 제약 조건 명
    ,b.constraint_type  -- P,R,U,C 
    ,a.column_name      -- 해당 컬럼
    ,a.position         -- 제약 조건 순서    
 from user_cons_columns a   -- 제약 컬럼
    , user_constraints b    -- 제약 조건
    -- dba_cons_columns a
    -- , dba_constraints b
where a.owner = b.owner
  and a.table_name = 'EMP'
  and a.constraint_name = b.constraint_name
  and b.constraint_type = 'P'
order by a.constraint_name, a.position

• 무결성 제약 조건

제약 조건	비고
NOT NULL	NULL 허용하지 않음
UNIQUE	해당 컬럼의 해당 값은 하나만 존재
PRIMARY KEY	해당 컬럼의 값은 반드시 있어야 하며, 하나만 존재
FOREIGN KEY	해당 컬럼이 참조하는 테이블에 있는 값만 입력 가능
CHECK	해당 컬럼에 저장 가능한 값의 조건을 지정

• user_constraints 속성

유형	비고
P	Primary Key
R	Foreign Key
U	UNIQUE
C	CHECK, NOT NULL

Comment 조회

  select 
    a.table_name    -- 테이블
    ,a.column_name  -- 컬럼 명
    ,a.comments     -- comment
 from user_col_comments a

컬럼 유형 및 PK, Comment

select 
    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       -- 자릿수 표현한 데이터 유형
    ,decode(a.nullable, 'Y', 'YES', 'NO') isnull            -- NULL 여부
    ,decode(pk.column_name, null, '', 'PRI') column_key     -- PK 여부 
    ,a.data_default -- 기본 값
    ,column_id      -- 생성 순서
    ,comm.comments  -- Comment    
from user_tab_columns a
    ,user_col_comments comm
    ,(
        select pkcol.table_name, pkcol.column_name 
          from user_cons_columns pkcol
             , user_constraints pk_con
        where pkcol.owner = pk_con.owner
        and pkcol.constraint_name = pk_con.constraint_name
        and pk_con.constraint_type = 'P'                
    ) pk
where a.TABLE_NAME = 'EMP'
  and a.TABLE_NAME = pk.table_name(+)
  and a.column_name = pk.column_name(+)
  and a.table_name = comm.table_name(+)
  and a.column_name = comm.column_name(+)
ORDER BY COLUMN_ID

#oracle #컬럼정보 #comment