要查看 MySQL 数据库中所有表的记录数和占用空间,您可以通过查询 INFORMATION_SCHEMA
数据库来获取这些信息。以下是具体步骤和SQL查询:
1. 查看所有表的记录数和占用空间
SELECT
table_schema AS '数据库名称',
table_name AS '表名称',
table_rows AS '记录数',
ROUND(data_length / 1024 / 1024, 2) AS '数据大小 (MB)',
ROUND(index_length / 1024 / 1024, 2) AS '索引大小 (MB)',
ROUND((data_length + index_length) / 1024 / 1024, 2) AS '总大小 (MB)'
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
ORDER BY
table_schema,
(data_length + index_length) DESC;
2. 查看所有表的存储过程
SELECT
ROUTINE_NAME AS procedure_name
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_SCHEMA = 'mydatabase'
AND ROUTINE_TYPE = 'PROCEDURE';
SELECT
ROUTINE_NAME AS procedure_name,
CREATED AS created_time,
LAST_ALTERED AS last_altered_time
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_SCHEMA = 'billionpay'
AND ROUTINE_TYPE = 'PROCEDURE'
ORDER BY last_altered_time DESC;