数据库、数据表信息

# 数据库信息
select s.schema_name,t.dbsize_mb,s.default_character_set_name,s.default_collation_name
from information_schema.schemata s
inner join(
select table_schema,round(sum((data_length+index_length)/1024/1024),2) as dbsize_mb from information_schema.tables group by table_schema
) t
on s.schema_name=t.table_schema
order by t.dbsize_mb desc;

# 数据表信息
select table_schema,table_name,table_type,engine,table_rows
    ,round((data_length+index_length)/1024/1024,2) as tbsize_mb
    ,round(data_length/1024/1024,2) as datasize_mb
    ,round(index_length/1024/1024,2) as indexsize_mb
    ,round(data_free/1024/1024,2) as freesize_mb
from information_schema.tables 
where table_schema='sakila'
order by tbsize_mb desc limit 30;

# 查看表、字段说明
select table_name,column_name,column_type,is_nullable,column_key,column_comment 
from(
select table_name,column_name,ordinal_position,column_type,is_nullable,column_key,column_comment from information_schema.columns where table_schema='sakila'
union all
select table_name,'','','','','',table_comment from information_schema.tables where table_schema='sakila'
) a
order by table_name,(ordinal_position+0);