How to: Get the size of your Databases in MySQL Query Browser

How to: Get the size of your Databases in MySQL Query Browser

Imagine you want to transfer data from one server to another and you need to know the size of the databases you are thinking of transfering. Unlike Microsoft SQL Server which graphical interface allows you to see the size of the database files and the amount of data stored MySQL WorkBench does not have a section that allows you to do just that (or at least I haven’t found it yet).  The good news is that you only need to run a simple query in order to obtain a table listing the different tables in your server as well as the size of the databases. Below is the SQL script:

SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”
FROM information_schema.TABLES GROUP BY table_schema ;

As you would expect the space available can also be queried via a SQL script on your Query Browser like so:

SELECT table_schema “Data Base Name”,
sum( data_length + index_length ) / 1024 /
1024 “Data Base Size in MB”,
sum( data_free )/ 1024 / 1024 “Free Space in MB”
FROM information_schema.TABLES
GROUP BY table_schema ;

Enhanced by Zemanta

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.