2020년 3월 12일 목요일

MySQL 테이블 정의서 조회 쿼리

SELECT
IFNULL(T2.TABLE_COMMENT, T1.TABLE_NAME) AS TABLE_COMMENT
, T1.TABLE_NAME, T1.ORDINAL_POSITION, T1.COLUMN_COMMENT
, T1.COLUMN_NAME, UPPER(T1.DATA_TYPE) AS DATA_TYPE
, (CASE T1.DATA_TYPE WHEN 'varchar' THEN T1.CHARACTER_MAXIMUM_LENGTH ELSE '' END) AS DATA_LENGTH
, (CASE T1.IS_NULLABLE WHEN 'NO' THEN 'Y' ELSE '' END) AS NOT_NULL
, (CASE T1.COLUMN_KEY WHEN 'PRI' THEN 'Y' ELSE '' END) AS IS_PK
, T1.COLUMN_DEFAULT

FROM (
    SELECT
    *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbzimyung'
    AND TABLE_NAME LIKE 'm_%'
    ORDER BY TABLE_NAME ASC, ORDINAL_POSITION ASC
) T1
INNER JOIN (
    SELECT
    TABLE_NAME, TABLE_COMMENT
    FROM `INFORMATION_SCHEMA`.`TABLES`
    WHERE TABLE_NAME LIKE 'm_%'
) T2 ON T2.TABLE_NAME = T1.TABLE_NAME

댓글 없음:

댓글 쓰기