MySQL.테이블정의서 추출 쿼리문

-- USE INFORMATION_SCHEMA ;      (DB들 선택하는 명령 입니다.  USE  DB명)

 

SELECT  A.TABLE_SCHEMA                       AS DB ,
         A.TABLE_NAME                         AS Table_ID,
         A.TABLE_COMMENT                      AS Table_NAME ,
         B.COLUMN_NAME                        AS Column_ID ,
         B.COLUMN_COMMENT                     AS Column_Name ,
         B.DATA_TYPE                          AS Data_Type ,
 --      B.CHARACTER_MAXIMUM_LENGTH           AS Col_Length,    -- CHAR       character type에 대해 문자 단위로 보고 싶으면  CHARACTER_MAXIMUM_LENGTH  를 사용하시면 됩니다.
--      B.CHARACTER_OCTET_LENGTH                AS Col_Length,    -- BYTE
         CASE B.DATA_TYPE  WHEN 'varchar'     THEN B.CHARACTER_OCTET_LENGTH
                           WHEN 'char'        THEN B.CHARACTER_OCTET_LENGTH
                           WHEN 'decimal'     THEN CONCAT('(', B.NUMERIC_PRECISION, ',', B.NUMERIC_SCALE, ')')
                           WHEN 'numeric'     THEN CONCAT('(', B.NUMERIC_PRECISION, ',', B.NUMERIC_SCALE, ')')
                           WHEN 'tinyint'     THEN B.NUMERIC_PRECISION
                           WHEN 'smallint'    THEN B.NUMERIC_PRECISION
                           WHEN 'mediumint'   THEN B.NUMERIC_PRECISION
                           WHEN 'int'         THEN B.NUMERIC_PRECISION
                           WHEN 'bigint'      THEN B.NUMERIC_PRECISION
                           WHEN 'date'        THEN B.DATETIME_PRECISION
                           WHEN 'datetime'    THEN B.DATETIME_PRECISION
                           WHEN 'time'        THEN B.DATETIME_PRECISION
                           WHEN 'timestamp'   THEN B.DATETIME_PRECISION
                           ELSE  B.CHARACTER_OCTET_LENGTH
         END      AS Col_Length ,
         B.COLUMN_DEFAULT                     AS Default_Value,
 --      B.IS_NULLABLE                        AS Is_Nullables,
         CASE B.IS_NULLABLE WHEN 'NO'  THEN 'N' WHEN 'YES' THEN 'Y' END AS Is_Null,
         CASE B.COLUMN_KEY  WHEN 'PRI' THEN 'Y' ELSE ''             END AS PK

FROM   INFORMATION_SCHEMA.TABLES   A,
              INFORMATION_SCHEMA.COLUMNS  B

WHERE  A.TABLE_NAME      = B.TABLE_NAME
    AND A.TABLE_SCHEMA    = B.TABLE_SCHEMA
    AND A.TABLE_TYPE    = 'BASE TABLE'
    AND A.TABLE_SCHEMA  in ('employees')
 --   AND A.TABLE_SCHEMA  in ('db')
 --   AND A.TABLE_SCHEMA  in ('ShopDB')
 --   AND A.TABLE_SCHEMA  in ('wideshot_service')
 order by Table_ID, B.ORDINAL_POSITION
 ;