mysql查询数据字典

--表级注释
SELECT
  '系统缩写' AS xx,
 '系统名称' AS ccc,
 '编号' AS id,
  b.TABLE_NAME,
  CONCAT('tb_',LOWER(b.TABLE_NAME)) AS TABLE_NAME,
  b.TABLE_COMMENT
FROM
  information_schema.SCHEMATA a
  INNER JOIN information_schema.TABLES b
    ON b.TABLE_SCHEMA = a.SCHEMA_NAME
    AND lower(a.SCHEMA_NAME)  = 'database_name'
 AND b.TABLE_NAME NOT LIKE 'v_%'
 AND LENGTH(TRIM(b.TABLE_COMMENT)) > 0 
ORDER BY  b.TABLE_NAME
;

-- 字段级注释
SELECT
  '系统缩写' AS xxx,
 '系统名称' AS xxxs,
  a.SCHEMA_NAME,
  b.TABLE_NAME,
   CONCAT('tb_',LOWER(b.TABLE_NAME)) AS stg_TABLE_NAME,
  b.TABLE_COMMENT,
  c.ORDINAL_POSITION,
  c.COLUMN_NAME,
  c.COLUMN_COMMENT,
  c.COLUMN_TYPE
FROM
  information_schema.SCHEMATA a
  INNER JOIN information_schema.TABLES b
    ON b.TABLE_SCHEMA = a.SCHEMA_NAME
    AND lower(a.SCHEMA_NAME)  = 'database_name'
 AND b.TABLE_NAME NOT LIKE 'v_%'
  INNER JOIN information_schema.COLUMNS c
    ON a.SCHEMA_NAME = c.TABLE_SCHEMA
    AND b.TABLE_NAME = c.TABLE_NAME
ORDER BY c.TABLE_SCHEMA,
  c.TABLE_NAME,
  c.ORDINAL_POSITION