1 表数量
select count(1)
from information_schema.tables a
where a.table_schema = 'db_name';
2 中文名
select a.table_name 表名,a.table_comment 表注释
from information_schema.tables a
where a.table_schema = 'db_name'
order by a.table_rows desc;
3 主键查询
select a.table_name 表名,
t_col.table_comment 表注释,
a.column_name 字段名,
a.column_comment 字段描述,
a.column_key 主键
from information_schema.COLUMNS a left join (SELECT table_name, table_comment
FROM information_schema.TABLES
WHERE table_schema = 'db_name'
group by table_name) t_col on a.table_name = t_col.table_name
where a.table_schema = 'db_name'
and a.column_key = 'PRI';
4 字段非空查询
select a.table_name 表名,
t_col.table_comment 表注释,
a.column_name 字段名,
a.column_comment 字段描述,
case is_nullable when 'NO' then '否' else '是' End 是否可空
from information_schema.COLUMNS a left join (SELECT table_name, table_comment
FROM information_schema.TABLES
WHERE table_schema = 'db_name'
group by table_name) t_col on a.table_name = t_col.table_name
where a.table_schema = 'db_name'
and a.is_nullable = 'NO';