mysql使用sql语句查询数据库所有表注释已经表字段注释

mysql使用sql语句查询数据库所有表注释已经表字段注释

 

场景：
1. 要查询数据库 "mammothcode" 下所有表名以及表注释
/* 查询数据库 ‘mammothcode’ 所有表注释 */

SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES 
WHERE table_schema='数据库名称' 
    and Table_Name ='表名称'

 

2. 要查询表字段的注释
/* 查询数据库 ‘mammothcode’ 下表 ‘t_adminuser’ 所有字段注释 */

SELECT TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME,
  case when exists(select * 
       from INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` t1
       where t0.table_schema=t1.table_schema 
        and t0.table_name=t1.table_name
        and t0.COLUMN_NAME=t1.COLUMN_NAME
        and t1.constraint_name='PRIMARY' ) then '主键' else '' END as 'PRIMARY',
  DATA_TYPE,COLUMN_TYPE,column_comment 
FROM INFORMATION_SCHEMA.Columns t0
WHERE table_schema='数据库名称'
 and table_name='表名' 
    order by TABLE_NAME,ORDINAL_POSITION