KINGBASE语句
SELECT
A.table_name "表名",
C.COMMENTS "表注释",
concat(C.COMMENTS,'(',A.table_name,')') "拼接字段",
A.column_name "字段名",
D.COMMENTS "字段注释",
upper(A.data_type) "字段类型",
coalesce(A.character_maximum_length, A.numeric_precision, -1) as "长度",
D.COMMENTS "备注"
FROM
information_schema.columns A
LEFT JOIN (
SELECT
sys_attribute.attname,
sys_class.oid
FROM
sys_index,
sys_class,
sys_attribute
WHERE sys_index.indrelid = sys_class.oid
AND sys_attribute.attrelid = sys_class.oid
AND sys_attribute.attnum = ANY (sys_index.indkey)
) B ON B.oid = A.table_name :: regclass AND A.column_name = b.attname
LEFT JOIN (SELECT TABLE_NAME,COMMENTS from all_tab_comments WHERE OWNER = '模式名称') C ON A.table_name = C.table_name
LEFT JOIN user_col_comments D ON A.table_name = D.table_name AND A.column_name = D.column_name
WHERE
A.table_schema = '模式名称'
ORDER BY
A.table_name,ordinal_position ASC
MySQL语句
SELECT
c.table_name 表名,
t.TABLE_COMMENT 表注释,
CONCAT(t.TABLE_COMMENT,'(',c.table_name,')') 拼接字段,
c.COLUMN_NAME 字段名,
c.COLUMN_COMMENT 字段注释,
UPPER(c.DATA_TYPE) 字段类型,
COALESCE(character_maximum_length, numeric_precision, -1) 长度,
c.COLUMN_COMMENT 备注
FROM
information_schema.COLUMNS c,
information_schema.TABLES t
WHERE
c.TABLE_SCHEMA = '数据库名称'
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
ORDER BY
c.table_name ASC,
c.COLUMN_NAME ASC;
后续会根据需求逐渐补充。