5 09 2024

1) alter문 생성

SELECT co.table_name

,co.column_name
,co.data_type
,CONCAT(‘alter table ‘ , co.table_name, ‘ CHANGE COLUMN ‘ , co.column_name , ‘ ‘ , co.column_name , ‘ VARCHAR(50) NULL DEFAULT NULL COMMENT “최초 등록자 정보”; ‘) AS ddl

FROM information_schema.columns co
INNER JOIN information_schema.tables tb
ON co.table_schema = tb.table_schema
AND co.table_name = tb.table_name
AND tb.table_schema = ‘goe_v1_db’
AND co.column_name = ‘frst_rgtr_info’
ORDER BY co.table_name

2)산출물쿼리

SELECT co.table_name AS 영문테이블명
,co.column_comment AS 한글컬럼명
,co.column_name AS 영문컬럼명
,'' AS 컴럼설명
,tb.table_comment AS 연관앤터티명
,co.column_comment AS 연관속성명
,co.data_type AS 데이터타입
,
concat(
case when co.column_name like '%_no' or co.column_name='no' then '번호'
when co.column_name like '%_info' then '정보'
when co.column_name like '%_id' or co.column_name = 'id' then '아이디'
when co.column_name like '%_nm%' or co.column_name = 'nm' then '이름'
when co.column_name like '%_addr' or co.column_name = 'addr' then '주소'
when co.column_name like '%_cd' then '코드'
when co.column_name like '%_yn' then '여부'
when co.column_name like '%_se' or co.column_name= 'se' then '구분'
when co.column_name like '%_path' then '경로'
when co.column_name like '%_type' then '타입'
when co.column_name like '%_url' or co.column_name = 'url' or co.column_name like 'url%' then 'url'
when co.column_name like '%_dt' then '일시'
when co.column_name like '%_atr' then '속성'
when co.column_name like '%_stts' or co.column_name = 'stts' then '상태'
when co.column_name like '%_ver' then '버전'
when co.column_name like '%_expln'or co.column_name = 'expln' then '설명'
when co.column_name like '%_sn' or co.column_name = 'sn' then '일련번호'
when co.column_name like '%_sz' then '크기'
when co.column_name like '%_ordr' then '순서'
when co.column_name like '%_len' then '길이'
when co.column_name like '%_level' then '레벨'
when co.column_name like '%_rqst' then '요청'
when co.column_name like '%_crtr' then '기준'
when co.column_name like '%_usqty' then '용량'
when co.column_name like '%_co' or co.column_name like '%_cnt' then '수'
when co.column_name like '%_ref' or co.column_name like 'ref_%' then '참고'
when co.column_name like '%_dfn' then '정의'
when co.column_name like '%_extn'then '확장자'
when co.column_name like '%_authrt'then '권한'
when co.column_name like '%_cls'then '클래스'
when co.column_name like '%_mthd'then '메소드'
when co.column_name like '%excp'then '예외'
when co.column_name like '%_data'then '데이터'
when co.column_name like '%_pswd'then '비밀번호'
when co.column_name like '%_link'then '링크'
when co.column_name like 'excp_%'then '예외'
when co.column_name like 'atrb%'then '속성'
when co.column_name like 'authrt_%'then '권한'
when co.column_name like 'rqst_%'then '요청'
when co.column_name like 'zip%'then '우편번호'
when co.column_name like 'mthd'then '메소드'
when co.column_name like 'cn'then '내용'
when co.column_name like '%_vl' or co.column_name = 'vl'then '값'
when co.column_name like 'wdth' or co.column_name = 'hgt'then '단위'
WHEN co.data_type = 'longtext' THEN '설명(longtext)'
WHEN co.data_type = 'text' THEN '설명(text)'
else CONCAT('명(V,', co.character_maximum_length, ')')
end,
CASE WHEN co.data_type = 'varchar' THEN CONCAT('(V,', co.character_maximum_length, ')')
WHEN co.data_type = 'char' THEN CONCAT('(C,', co.character_maximum_length, ')')
WHEN co.data_type = 'bigint' THEN CONCAT('(BIGINT)')
WHEN co.data_type = 'datetime' THEN CONCAT('(DATETIME)')
WHEN co.data_type = 'decimal' THEN CONCAT('(D)')
WHEN co.data_type = 'int' THEN CONCAT('(INT)')
WHEN co.data_type = 'longtext' THEN CONCAT('(LONGTEXT)')
WHEN co.data_type = 'smallint' THEN CONCAT('(SMALLINT)')
WHEN co.data_type = 'text' THEN CONCAT('(TEXT)')
END
)

3)Column 확인 쿼
SELECT co.table_name
,tb.table_comment
,co.column_name
,co.data_type
,co.column_comment
,co.CHARACTER_MAXIMUM_LENGTH
,co.numeric_precision
,co.numeric_scale
,co.COLUMN_DEFAULT

FROM information_schema.columns co
INNER JOIN information_schema.tables tb
ON co.table_schema = tb.table_schema
AND co.table_name = tb.table_name
AND tb.table_schema = 'goe_v1_db'
ORDER BY co.table_name ASC


Linux Apache Free SSL 적용방법