Shows a dB tables size in MB #mysql #shell

checkDatabaseTablesSize.sh

#!/bin/sh

# shows a dB tables size in MB
# usage : checkDatabaseTablesSize.sh databasename
# mysql : SELECT TABLE_NAME,(DATA_LENGTH+INDEX_LENGTH)/1024/1024 FROM information_schema.TABLES WHERE table_schema="dbname";

DATABASE=$1
mysql -e "SELECT TABLE_NAME,(DATA_LENGTH+INDEX_LENGTH)/1024/1024 FROM information_schema.TABLES WHERE table_schema=\"${DATABASE}\";"

showAllDatabasesSizes.sql

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 ORDER BY sum( data_length + index_length);

showDatabaseTablesSize.sql

SELECT TABLE_NAME,(DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS Size FROM information_schema.TABLES WHERE table_schema="DATABASE" ORDER BY Size DESC;

SELECT TABLE_NAME,(DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS Size, table_rows FROM information_schema.TABLES WHERE table_schema="DATABASE" ORDER BY Size DESC;