数据库表概要信息统计sql
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.tablest1
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;
页:
[1]