SELECT
c.TABLE_NAME AS tableName
,c.ORDINAL_POSITION AS no
,c.COLUMN_KEY AS keyPrefix
,IF(idx.INDEX_NAME IS NULL, null,
CASE WHEN idx.INDEX_NAME = 'PRIMARY' THEN 'PK'
WHEN idx.NON_UNIQUE = 0 THEN 'UK'
ELSE idx.INDEX_NAME
END
) AS idx_name
,c.COLUMN_NAME AS name
,c.DATA_TYPE AS dataType
,IF(c.NUMERIC_SCALE IS NOT NULL,
CONCAT(c.NUMERIC_PRECISION, ',', c.NUMERIC_SCALE),
IF(c.NUMERIC_PRECISION IS NOT NULL,
c.NUMERIC_PRECISION,
c.CHARACTER_MAXIMUM_LENGTH)
) AS length
,IF( c.IS_NULLABLE = 'YES', true, false ) AS nullable
, CASE
WHEN c.IS_NULLABLE = 'YES' THEN ''
ELSE
CASE
WHEN c.EXTRA IS NULL AND c.COLUMN_DEFAULT IS NOT NULL
THEN c.COLUMN_DEFAULT
WHEN c.EXTRA IS NOT NULL AND c.COLUMN_DEFAULT IS NULL
THEN c.EXTRA
WHEN c.EXTRA IS NOT NULL AND c.COLUMN_DEFAULT IS NOT NULL
THEN CONCAT(c.COLUMN_DEFAULT, ' ', c.EXTRA)
ELSE c.COLUMN_DEFAULT
END
END AS defaultValue
,c.COLUMN_COMMENT AS comment
FROM information_schema.COLUMNS c
LEFT JOIN information_schema.STATISTICS idx
ON idx.TABLE_NAME=c.TABLE_NAME AND idx.COLUMN_NAME=c.COLUMN_NAME
WHERE
c.TABLE_SCHEMA = 'xxxxx'
ORDER BY
c.TABLE_NAME,c.ORDINAL_POSITION;