主要是通过 information_schema 库的如下表完成统计：

对象属性：
    tables：存放了所有表的元数据信息；
    columns：存放了所有表的列的元数据信息；

tables 表

列描述：

TABLE_CATALOG    varchar(512)    NO            
TABLE_SCHEMA    varchar(64)    NO                -- 表所在的库
TABLE_NAME    varchar(64)    NO                    -- 表名
TABLE_TYPE    varchar(64)    NO            
ENGINE    varchar(64)    YES                        -- 存储引擎
VERSION    bigint(21) unsigned    YES            
ROW_FORMAT    varchar(10)    YES            
TABLE_ROWS    bigint(21) unsigned    YES             -- 表行数
AVG_ROW_LENGTH    bigint(21) unsigned    YES         -- 平均行长度    
DATA_LENGTH    bigint(21) unsigned    YES            
MAX_DATA_LENGTH    bigint(21) unsigned    YES            
INDEX_LENGTH    bigint(21) unsigned    YES         -- 索引长度    
DATA_FREE    bigint(21) unsigned    YES             -- 碎片的大小
AUTO_INCREMENT    bigint(21) unsigned    YES            
CREATE_TIME    datetime    YES            
UPDATE_TIME    datetime    YES            
CHECK_TIME    datetime    YES            
TABLE_COLLATION    varchar(32)    YES            
CHECKSUM    bigint(21) unsigned    YES            
CREATE_OPTIONS    varchar(255)    YES            
TABLE_COMMENT    varchar(2048)    NO            

例子

1、查看每个业务库表的个数和名称。

select table_schema,COUNT(1),group_concat(table_name)
from information_schema.tables 
where table_schema not in ('mysql','information_schema','performance_schema','sys')
group by table_schema;

2、统计每个库的数据量大小。

select table_schema,concat(sum(table_rows*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,'K') as total_size
from information_schema.tables 
where table_schema not in ('mysql','information_schema','performance_schema','sys')
group by table_schema;

3、查询业务表中，所有不是 InnoDB 引擎的表。

select table_schema,table_name
from information_schema.tables 
where table_schema not in ('mysql','information_schema','performance_schema','sys')
and engine <> 'InnoDB'

4、命令拼接。

select concat('alter table ',table_schema,'.',table_name,' engine=innodb')
from information_schema.tables 
where table_schema not in ('mysql','information_schema','performance_schema','sys')
into outfile '/tmp/alter.sql'
-- 报错则需要设置 secure_file_priv=/tmp/

columns 表

列描述：

TABLE_CATALOG    varchar(512)    NO            
TABLE_SCHEMA    varchar(64)    NO                            -- 库名
TABLE_NAME    varchar(64)    NO                                -- 表名
COLUMN_NAME    varchar(64)    NO                                -- 列名
ORDINAL_POSITION    bigint(21) unsigned    NO 0            -- 列位置
COLUMN_DEFAULT    longtext    YES                            -- 默认值
IS_NULLABLE    varchar(3)    NO                                -- 是否可空
DATA_TYPE    varchar(64)    NO                                -- 字段数据类型
CHARACTER_MAXIMUM_LENGTH    bigint(21) unsigned    YES         -- 最大长度
CHARACTER_OCTET_LENGTH    bigint(21) unsigned    YES            
NUMERIC_PRECISION    bigint(21) unsigned    YES            
NUMERIC_SCALE    bigint(21) unsigned    YES            
DATETIME_PRECISION    bigint(21) unsigned    YES            
CHARACTER_SET_NAME    varchar(32)    YES            
COLLATION_NAME    varchar(32)    YES                            -- 排序规则
COLUMN_TYPE    longtext    NO                                -- 字段数据类型带长度
COLUMN_KEY    varchar(3)    NO                                -- 索引
EXTRA    varchar(30)    NO            
PRIVILEGES    varchar(80)    NO            
COLUMN_COMMENT    varchar(1024)    NO                        -- 注释
GENERATION_EXPRESSION    longtext    NO            

例子

1、查询全库中所有业务表的数据字典信息，库名、表名、列、数据类型、索引、注释

select table_schema as 'dbname', table_name as 'tblname', column_name as 'colname',data_type as 'type',column_key as 'index',column_comment as 'comment' 
    from columns 
    where table_schema not in ('mysql', 'information_schema', 'performance_schema','sys');