【MySQL】MySQL查数据库中每个表的数据量
一、方法1，数据字典表/* Mysql查数据库中每个表的数据量 */
SELECT table_schema
,table_name
,table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
二、方法2，存过，循环count()每张表
drop PROCEDURE if exists GetAllTableCounts;
CREATE PROCEDURE GetAllTableCounts()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE aDatabase CHAR(64);
DECLARE aTable CHAR(64);
DECLARE dateString VARCHAR(23);
DECLARE cur1 CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');
DECLARE cur2 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = aDatabase;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
set @sqlString = 'select ''数据库名'' as dbname,''表名'' as tablename,''数量'' as count ';
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO aDatabase;
IF done THEN
LEAVE read_loop;
END IF;
OPEN cur2;
table_loop: LOOP
FETCH cur2 INTO aTable;
IF done THEN
LEAVE table_loop;
END IF;
set @sqlString = CONCAT(@sqlString ,' union all select ''',aDatabase,''' as dbname, ''',aTable,''' as tablename, count(*) from ',aDatabase,'.',aTable);
END LOOP table_loop;
CLOSE cur2;
SET done = FALSE;
END LOOP read_loop;
CLOSE cur1;
select @sqlString;
PREPARE stmt FROM @sqlString;
execute stmt;
DEALLOCATE PREPARE stmt;
END;
-- 调用方式1
-- call GetAllTableCounts();
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]