Table / Column Comment 추출하는 쿼리

안녕하세요.

오늘은 (제 스타일의) ERD 작성시 Table / Column Comment 를 추출하는 쿼리 공유드리고자 합니다.

 

저희가 지원하는 서비스의 경우, Life Time 이 너무 짧아 ERD 를 꾸준히 관리하지는 못하지만,

초기 서비스를 시작할 때 관련담당자들(DB, DEV, DW, 사업PM)의 이해도를 높이기 위해 ERD 를 그리곤 합니다.

 

경험상 대부분의 모델링 도구들이 '한글' 또는 '영문'명만 표시해주면서 실제 코딩시 활용도가 낮은데,

"영문컬럼명 [한글정의]" 형태로 ERD 를 그림으로써 다소나마 가시성을 높여 사용하고 있습니다.

(생각해보니 DA# 에서 이런 형태로 보여주는 기능이 있네요. Padding 이 안되어 이쁘진 않지만... ㅎㅎ)

 

■ ERD 예제

 

 

 

■ 추출쿼리

=====================================
쿼리 Type
=====================================

select concat(rpad(column_name, a.start_no, ' '),'[',trim(b.column_comment),']') as col_desc
     , b.table_schema, b.table_name, b.column_name, b.column_comment, a.start_no
  from (select table_schema, table_name, max(length(column_name))+2 as start_no
          from information_schema.columns
         where table_schema not in ('information_schema','common_schema','mysql','performance_schema','ps_helper')
         group by table_schema, table_name) a
      , information_schema.columns b
 where a.table_schema = b.table_schema
   and a.table_name   = b.table_name
 order by b.table_schema, b.table_catalog, b.table_schema, b.table_name, b.ordinal_position;

show full columns from information_schema.columns;

 

=====================================
프로시져 Type
=====================================

use test;

DROP PROCEDURE IF EXISTS usp_show_table_desc;

delimiter //

CREATE PROCEDURE usp_show_table_desc()
BEGIN

   DECLARE done           INT DEFAULT 0;

   DECLARE v_table_schema VARCHAR(50);
   DECLARE v_table_name   VARCHAR(50);
   DECLARE v_max_length   INT;

   DECLARE cur1 CURSOR
   FOR 
   select table_schema, table_name, max(length(column_name))+2 as max_length
     from information_schema.columns
    where table_schema not in ('information_schema','common_schema','mysql','performance_schema','ps_helper')
    group by table_schema, table_name
    order by table_schema, table_name;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

   OPEN cur1;

   read_loop:
   LOOP

      FETCH cur1 INTO v_table_schema, v_table_name, v_max_length;

      -- select v_table_schema, v_table_name, v_max_length;

      select "===============================================" as "___comments___"
       union
      select concat(table_name, ' [',trim(table_comment),']')  as "___comments___"
        from information_schema.tables
       where table_schema = v_table_schema
         and table_name   = v_table_name
       union all
      select "==============================================="
       union all
      select concat(rpad(column_name, v_max_length, ' '),'[',trim(column_comment),']') as col_desc
        from (
             select column_name, column_comment
               from information_schema.columns
              where table_schema = v_table_schema
                and table_name   = v_table_name
              order by ordinal_position
             ) x
       union all
      select "==============================================="
       union all
      select ""
       union all
      select "";

      IF done = 1 THEN
         LEAVE read_loop;
      END IF;

   END LOOP;

   CLOSE cur1;

END
//

delimiter ;

call usp_show_table_desc();

 

-- shell 에서 실행

mysql -u root -p --skip-column-names -e "use test; call usp_show_table_desc();" > tab_col_desc.txt

-- Output 확인

vi tab_col_desc.txt
-------------------------------------------------------------------
...
===============================================
user_stage [유저별 스테이지 정보]
===============================================
usr_id        [유저 고유번호]
stage_id      [스테이지 고유번호]
stage_level   [스테이지 현재등급]
wins          [(누적) 승수]
kills         [(누적) 킬수]
===============================================
...
-------------------------------------------------------------------

 

[출처] Table / Column Comment 추출하는 쿼리 (MySQL Power Group) |작성자 준경대디