DB 테이블 컬럼정보 조회

올로시아·2023년 10월 2일

Database Snake case To Camel case Online Converter

코드 한땀

목록 보기

3/16

지난 시간에는 올로시아 코드 한땀 서비스에서 지원하는 입력유형을 안내해드렸는데요.

오늘은 입력유형 중 DB 테이블 컬럼정보를 이용하는 방법에 대해 말씀드리려고 합니다.

코드 한땀 서비스 페이지 하단부의 Columns 탭을 선택하시면 위와 같은 화면을 보실 수 있습니다.

각 Database 종류별로 DB 테이블 컬럼정보를 조회하는 쿼리를 제공하고 있습니다.

옵션 항목 중 table-name 이라는 input 항목에 조회하실 테이블명을 입력하시면 위 쿼리 중 'olosia_com' 으로 들어간 값이 대체됩니다.

지금은 MySQL, PostgreSQL, Oracle 세가지에 대해 제공하고 있는데요.

하나씩 살펴보겠습니다.

MySQL

SELECT C.COLUMN_NAME,
       C.COLUMN_TYPE,
       C.COLUMN_COMMENT
 /* ,
       CASE WHEN C.IS_NULLABLE = 'NO' THEN 'N' ELSE 'Y' END NULL_YN,
       CASE WHEN C.COLUMN_KEY = 'PRI' THEN 'Y' ELSE 'N' END PK_YN */
  FROM INFORMATION_SCHEMA.TABLES T
       LEFT JOIN INFORMATION_SCHEMA.COLUMNS C
              ON T.TABLE_NAME = C.TABLE_NAME
 WHERE 1 = 1
/* AND T.TABLE_SCHEMA = 'TABLE_SCHEMA' */
   AND T.TABLE_NAME   = 'olosia_com'
 ORDER BY C.ORDINAL_POSITION;

PostgreSQL

SELECT PA.ATTNAME AS COLUMN_NAME,
       (CASE WHEN IC.CHARACTER_MAXIMUM_LENGTH > 0 
                     THEN IC.UDT_NAME || '(' || IC.CHARACTER_MAXIMUM_LENGTH || ')'
                 ELSE IC.UDT_NAME
         END) AS COLUMN_TYPE,
       PD.DESCRIPTION AS COLUMN_COMMENT
 /* ,
       (CASE WHEN IC.IS_NULLABLE = 'YES' THEN 'Y' ELSE 'N' END) AS NULL_YN,
       (CASE WHEN (   SELECT CC.COLUMN_NAME
						FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CC
                              ON CC.TABLE_CATALOG = TC.TABLE_CATALOG
                             AND CC.TABLE_SCHEMA = TC.TABLE_SCHEMA
                             AND CC.TABLE_NAME = TC.TABLE_NAME
                             AND CC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
                             AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
                       WHERE TC.TABLE_CATALOG = IC.TABLE_CATALOG
                         AND TC.TABLE_NAME = IC.TABLE_NAME
                         AND CC.COLUMN_NAME = IC.COLUMN_NAME
                   ) = IC.COLUMN_NAME THEN 'Y' ELSE 'N'
         END) AS PK_YN */
  FROM PG_STAT_ALL_TABLES AS PT
      JOIN PG_DESCRIPTION AS PD 
           ON PD.OBJOID = PT.RELID AND PD.OBJSUBID <> 0
      JOIN PG_ATTRIBUTE AS PA 
           ON PA.ATTRELID = PD.OBJOID AND PA.ATTNUM = PD.OBJSUBID
      JOIN INFORMATION_SCHEMA.COLUMNS AS IC 
           ON IC.TABLE_SCHEMA = PT.SCHEMANAME AND IC.TABLE_NAME = PT.RELNAME AND IC.COLUMN_NAME = PA.ATTNAME
 WHERE 1 = 1
/* AND IC.TABLE_CATALOG = 'postgres'
   AND PT.SCHEMANAME = 'public' */
   AND PT.RELNAME = 'olosia_com'
 ORDER BY PT.RELNAME, PD.OBJSUBID;

Oracle

SELECT C.COLUMN_NAME,
       C.DATA_TYPE AS COLUMN_TYPE,
       (SELECT T.COMMENTS
          FROM ALL_COL_COMMENTS T
         WHERE T.OWNER = C.OWNER
           AND T.TABLE_NAME = C.TABLE_NAME
           AND T.COLUMN_NAME = C.COLUMN_NAME) AS COLUMN_COMMENT
 /* ,
       B.DATA_TYPE || CASE
                          WHEN B.DATA_PRECISION IS NOT NULL
                          THEN
                             '(' || B.DATA_PRECISION || ',' || B.DATA_SCALE || ')'
                          ELSE
                             '(' || B.DATA_LENGTH || ')'
                       END AS COLUMN_TYPE,
        DECODE(B.NULLABLE, 'Y', 'Y', 'N') AS NULL_YN,
        DECODE((SELECT 'PK'
           FROM ALL_CONSTRAINTS X, ALL_CONS_COLUMNS Y
          WHERE X.OWNER = Y.OWNER
            AND X.TABLE_NAME = Y.TABLE_NAME
            AND X.CONSTRAINT_NAME = Y.CONSTRAINT_NAME
            AND X.CONSTRAINT_TYPE = 'P'
            AND Y.OWNER = B.OWNER
            AND Y.TABLE_NAME = B.TABLE_NAME
            AND Y.COLUMN_NAME = B.COLUMN_NAME), 'PK', 'Y', 'N')
           AS PK_YN */
  FROM ALL_TABLES A, ALL_TAB_COLUMNS C
 WHERE A.OWNER = C.OWNER
   AND A.TABLE_NAME = C.TABLE_NAME
/* AND A.OWNER = 'OWNER' */
   AND A.TABLE_NAME = 'olosia_com'
 ORDER BY A.OWNER, A.TABLE_NAME, C.COLUMN_ID;