数据库表概要信息统计sql

jericho0702

SELECT t1.table_schema,t1.table_name,`ENGINE`,table_rows,CAST(data_length/1024.0/1024.0 AS DECIMAL(10,2)) `data_size(M)`,　　
CAST(index_length/1024.0/1024.0 AS DECIMAL(10,2)) `index_size(M)`,
　　
t2.ct col_count,t3.ct idx_count,create_time,table_comment
　　
FROM information_schema.tables  t1
　　
LEFT JOIN
　　
-- 字段总数
　　
(SELECT table_name,COUNT(1) ct FROM information_schema.columns
　　
GROUP BY table_name
　　
) t2
　　
ON t1.table_name=t2.table_name
　　
LEFT JOIN
　　
-- 索引总数
　　
(SELECT table_name,COUNT(DISTINCT index_name) ct FROM information_schema.STATISTICS
　　
GROUP BY table_name
　　
) t3
　　
ON t1.table_name=t3.table_name
　　
WHERE t1.table_schema NOT IN ('mysql','information_schema','performance_schema')
　　
ORDER BY t1.data_length DESC;