mysql查看表

 查看表结构

--查看表结构

select

a.table_name,a.column_name,a.column_type,a.column_comment,a.column_key

from

INFORMATION_SCHEMA.COLUMNs a where a.table_name = 'UEBMI_CLCT_DETL_B';

show full COLUMNS from UEBMI_CLCT_DETL_B;

show full columns from UEBMI_CLCT_DETL_EXT_B;

 查看表注解

--查看表注释

SELECT

table_name,

table_comment

FROM

INFORMATION_SCHEMA.`TABLES` a

WHERE

a.table_name = 'EMP_INSU_D'

结构转化

SELECT

'' AS 序号,

a.column_comment AS 中文名,

a.COLUMN_name AS 英文名,

CASE

IS_NULLABLE

WHEN 'YES' THEN

'是' ELSE '否'

END AS 可为空,

CASE

LEFT ( a.COLUMN_TYPE, 4 )

WHEN 'varc' THEN

'String'

WHEN 'deci' THEN

'BigDecimal'

WHEN 'date' THEN

'Date' ELSE 'String'

END AS 类型,

CASE

LEFT ( a.COLUMN_TYPE, 4 )

WHEN 'varc' THEN

a.CHARACTER_MAXIMUM_LENGTH

WHEN 'deci' THEN

CONCAT( '(', a.NUMERIC_PRECISION, ',', a.NUMERIC_SCALE, ')' ) ELSE ''

END AS 值范围,

'' AS 说明

FROM

INFORMATION_SCHEMA.COLUMNS a

WHERE

a.table_name = 'UEBMI_CLCT_DETL_b';