.::: Create Store procedure for database & size capacity in MariaDB, MySQL :::.

 1. referlink

https://teguhth.blogspot.com/2020/12/database-size-table-capacity-in-mysql.html
http://teguhth.blogspot.com/2022/08/create-store-procedure-for-database.html
https://teguhth.blogspot.com/2022/08/create-event-scheduler-to-run-query.htm

 

A. Database size info
2. create store procedure for Database size capacity

 
use teguhthtools;

DELIMITER //
Create Procedure sp_database_size_info()

begin

insert into teguhthtools.tbl_dbsize
SELECT @@hostname as ServerName, current_timestamp() as DateTime,s.schema_name as SchemaName,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00)) TotalSizeMB,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00)) DataUsedMB,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00)) DataFreeMB  
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema GROUP BY s.schema_name ORDER BY SchemaName ;


end //

DELIMITER ;
 3. call Database size capacity sp_database_size_info()

call sp_database_size_info();

4. create store procedure for read table teguhthtools.tbl_dbsize

use teguhthtools;

DELIMITER //
Create Procedure read_database_size_info()

begin

select * from teguhthtools.tbl_dbsize order by DateTime desc;

end //

DELIMITER;

5. call report read_database_size_info()

call read_database_size_info();


B. table size info

2. create store procedure for table size capacity
use teguhthtools;

DELIMITER //
Create Procedure sp_table_size_info()

begin

insert into teguhthtools.tbl_tablesizeinfo
SELECT @@hostname as ServerName,current_timestamp() as DateTime,
    table_schema,
    table_name,
    table_type,
    row_format,
    table_rows,
    round(1.0*data_length/1024/1024, 2) as DataSizeMB,
    round(index_length/1024/1024, 2) as IndexSizeMB,
    round(data_free/1024/1024, 2) as FreeSizeMB,
    round((data_length + index_length)/1024/1024, 2) as TotalSizeMB
FROM
    information_schema.tables
WHERE
 table_type ='base table' and table_schema not in('performance_schema','mysql','information_schema')
ORDER BY table_name;


end //

DELIMITER ;

3. call table size capacity sp_table_size_info()

call sp_table_size_info();

4. create store procedure for read table teguhthtools.tbl_tablesizeinfo

use teguhthtools;

DELIMITER //
Create Procedure read_table_size_info()

begin

select * from teguhthtools.tbl_tablesizeinfo order by DateTime desc;

end //

DELIMITER;

5. call report read_table_size_info()

call read_table_size_info();

C. List store procedure     

1. check store procedure

select specific_name,routine_schema,routine_type, routine_definition
 from information_schema.routines
where  routine_type in ('PROCEDURE','function') AND routine_schema = 'teguhthtools';
        
or

select specific_name,routine_schema,routine_type
 from information_schema.routines
where  routine_type in ('PROCEDURE','function') AND routine_schema = 'teguhthtools';


MariaDB [teguhthtools]> select specific_name,routine_schema,routine_type
    ->  from information_schema.routines
    -> where  routine_type in ('PROCEDURE','function') AND routine_schema = 'teguhthtools';
+-------------------------+----------------+--------------+
| specific_name           | routine_schema | routine_type |
+-------------------------+----------------+--------------+
| read_database_size_info | teguhthtools   | PROCEDURE    |
| read_table_size_info    | teguhthtools   | PROCEDURE    |
| sp_database_size_info   | teguhthtools   | PROCEDURE    |
| sp_table_size_info      | teguhthtools   | PROCEDURE    |
+-------------------------+----------------+--------------+
4 rows in set (0.00 sec)

MariaDB [teguhthtools]>