SELECT
c.class_name AS tbl_nm,
a.attr_name AS attr_nm,
CONCAT(
CASE
WHEN t.type_name = 'STRING'
THEN 'VARCHAR'
ELSE t.type_name
END,
CASE
WHEN t.type_name = 'NUMERIC'
THEN CONCAT('(', REPLACE(FORMAT(d.prec, 0), ',', ''), ', ', REPLACE(FORMAT(d.scale, 0), ',', ''), ')')
WHEN t.type_name = 'ENUM'
THEN ''--CAST(d.enumeration AS VARCHAR)
/* ENUM 컬럼의 선언 부를 VARCHAR 로 변환 안됨 사후 처리 필요 */
ELSE CONCAT('(', REPLACE(FORMAT(d.prec, 0), ',', ''), ')')
END) AS attr_def,
CASE WHEN a.is_nullable = 1
THEN 'Y'
ELSE ''
END
AS attr_null,
/*
9.x 하위 버전 */
IF(a.data_type IN (4, 25, 26, 27, 35),
SUBSTRING_INDEX((SELECT coll_name FROM _db_collation coll WHERE coll.coll_id = [d].[code_set]),'_',1),'') AS attr_char,
IF(a.data_type IN (4, 25, 26, 27, 35),
(SELECT coll_name FROM
_db_collation coll
WHERE coll.coll_id = [d].[code_set]),'') AS attr_coll,
/*
10.x 전용 쿼리 */
-- IF(a.data_type
IN (4, 25, 26, 27, 35),
-- (SELECT
ch.charset_name FROM _db_charset ch WHERE d.code_set = ch.charset_id), '') AS
attr_charset,
-- IF(a.data_type
IN (4, 25, 26, 27, 35),
-- (SELECT
coll.coll_name FROM _db_collation coll WHERE d.collation_id = coll.coll_id),
'') AS attr_collation,
a.default_value,
/*
9.x 하위 버전 */
(SELECT [description] FROM
_cub_schema_comments cmt
WHERE cmt.table_name = c.class_name AND cmt.column_name = a.attr_name) AS col_cmt,
/*
10.x 전용 쿼리 */
-- a.comment,
FORMAT(
CASE
WHEN t.type_name = 'SHORT' THEN 2.0
WHEN t.type_name = 'INTEGER' THEN 4.0
WHEN t.type_name = 'BIGINT' THEN 8.0
WHEN t.type_name = 'NUMERIC' THEN 16.0
WHEN t.type_name = 'FLOAT' THEN 4.0
WHEN t.type_name = 'DOUBLE' THEN 8.0
WHEN t.type_name = 'MONETARY' THEN 12.0
WHEN t.type_name IN ('CHAR', 'STRING', 'VARCHAR') THEN d.prec
WHEN t.type_name IN ('TIMESTAMP','TIMESTAMPLTZ') THEN 4.0
WHEN t.type_name = 'DATE' THEN 4.0
WHEN t.type_name = 'TIME' THEN 4.0
WHEN t.type_name IN ('DATETIME','DATETIMELTZ','TIMESTAMPTZ') THEN 8.0
WHEN t.type_name = 'DATETIMETZ' THEN 12.0
WHEN t.type_name = 'BIT' THEN FLOOR(d.prec / 8.0)
WHEN t.type_name = 'BIT VARYING' THEN FLOOR(d.prec / 8.0)
ELSE 0
END, 0) || ' Byte' AS attr_byte
FROM
_db_class
c,
_db_attribute
a,
_db_domain
d,
_db_data_type
t
WHERE
a.class_of = c
AND
d.object_of = a
AND
d.data_type = t.type_id
AND
c.is_system_class = 0
AND
c.class_name = 'a'
ORDER BY
c.class_name,
a.def_order