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 ('epai')
-- 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;
'DataBase > MariaDB' 카테고리의 다른 글
MARIADB(MYSQL) DATA CHANGE THE PATH-WINDOWS(경로변경) (0) | 2020.12.08 |
---|---|
MariaDB(MYSQL) 최적화 하기 – My.Cnf(InnoDB) 설정 (0) | 2020.12.08 |
MySql 테이블 타입 종류 및 설명 (0) | 2020.12.07 |
Oracle에서 MariaDB로 마이그레이션하기 (0) | 2020.10.06 |
[Mysql] mysql 반복문, for문, loop 사용법 (0) | 2020.09.23 |