[Redshift]테이블 컬럼 정보 조회(컬럼desc, PK, 컬럼 사이즈 등)

안녕하세요. 잇킹 시도르입니다.

 

프로젝트를 진행하다 보면 문서 작성이나, 원천 시스템 분석을 위해서 테이블 스키마 조회를 자주 하게 됩니다. 이때 테이블 정보를 조회하기 위해서 각 DBMS들의 메타 테이블을 조회하는 쿼리를 자주 사용합니다.

오라클이면 ALL_TAB_COLUMNS과 같은 시스템 테이블이나 VIEW, MYSQL/MariaDB는 information_schema의 테이블을 이용해서 스키마 정보를 조회하기 위한 쿼리를 짜게 되죠.

 

Postgresql을 기반으로 하는 Redshift도 마찬가지로 스키마 메타 데이터를 저장하는 테이블이 있으며, 데이터베이스 개발자 안내서를 확인해보니 시스템 카탈로그 테이블이라고 하네요. 

docs.aws.amazon.com/ko_kr/redshift/latest/dg/c_intro_catalog_views.html

시스템 카탈로그 테이블 - Amazon Redshift

 

시스템 카탈로그 테이블은 여러 개로 나뉘어 있으며, 내가 필요로 하는 스키마 정보가 하나의 테이블에 다 있으면 좋겠지만, 그렇지 않은 경우도 많겠죠. 이번에 스키마 조회 쿼리를 짜려고 하는데 생각보다 정보를 찾기가 쉽지가 않았습니다. 그래서 스키마 정보를 조회하기 위한 쿼리를 남기려고 합니다. 

 

SELECT c.table_name 
      ,pdt.description AS table_desc
      ,c.column_name 
      ,pdc.description AS col_desc
      ,CASE WHEN p.column_name IS NOT NULL THEN 'Y' END AS pk
      ,CASE WHEN c.is_nullable = 'NO' THEN 'N' END AS null_yn
      ,CASE WHEN c.data_type = 'character varying' THEN 'VARCHAR' 
            WHEN c.data_type = 'numeric' THEN 'DECIMAL'  
            WHEN c.data_type LIKE '%timestamp%' THEN 'TIMESTAMP'
            END AS data_type
      ,CASE WHEN c.data_type = 'character varying' THEN text(character_maximum_length) 
            WHEN c.data_type = 'numeric' THEN text(numeric_precision) || ','||text(numeric_scale)  END AS len
  FROM information_schema.columns c 
  JOIN pg_catalog.pg_namespace n 
    ON c.table_schema = n.nspname
  JOIN pg_catalog.pg_class pc
    ON c.table_name  = pc.relname
   AND n.oid = pc.relnamespace   
  JOIN pg_catalog.pg_description pdt
    ON pc.oid = pdt.objoid
   AND pdt.objsubid = 0
  JOIN pg_catalog.pg_description pdc 
    ON c.ordinal_position = pdc.objsubid
   AND pc.oid = pdc.objoid
  LEFT JOIN ( -- pk find
              SELECT tco.constraint_schema
                    ,tco.constraint_name
                    --,kcu.ordinal_position as position
                    ,kcu.column_name
                    ,kcu.table_name
                from information_schema.table_constraints tco
                join information_schema.key_column_usage kcu 
                  on kcu.constraint_name = tco.constraint_name
                 and kcu.constraint_schema = tco.constraint_schema
                 and kcu.constraint_name = tco.constraint_name
               where tco.constraint_type = 'PRIMARY KEY'
                ) p
    ON c.table_schema = p.constraint_schema 
   AND c.table_name = p.table_name 
   AND c.column_name = p.column_name
 WHERE c.table_catalog ='DB명'
   AND c.table_schema  = '스키마명'  
   --AND c.table_name = '테이블명'
 ORDER BY c.table_name 
          ,c.ordinal_position 

 

해당 쿼리는 해당 스키마에 속한 테이블들의 여러 정보(테이블명, 테이블 Desc, 칼럼명, 칼럼 Desc, PK, NOT Null, 타입과 사이즈)를 조회하는 쿼리입니다. 저도 매번 다시 짜지 않으려고 저장해 놓은 쿼리로 앞으로도 유용하게 사용해야겠네요.

 

이상입니다!!!