mysql相关的一些语句

要查看 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;

 

发表评论

邮箱地址不会被公开。