I've written a small shell script to report how much disk space each table in a given MySQL database is using. For example, below is the output of the script when run against this site's database:
bmd /~: ./dbSize.sh coderholic root ********
wp_comments Data: 6.60MB Indexes: .15MB Total: 6.75MB
wp_links Data: 0MB Indexes: 0MB Total: 0MB
wp_options Data: 1.57MB Indexes: .01MB Total: 1.58MB
wp_postmeta Data: .01MB Indexes: .01MB Total: .02MB
wp_posts Data: .57MB Indexes: .02MB Total: .60MB
wp_term_relationships Data: 0MB Indexes: .01MB Total: .02MB
wp_term_taxonomy Data: 0MB Indexes: 0MB Total: 0MB
wp_terms Data: 0MB Indexes: 0MB Total: 0MB
wp_tla_data Data: 0MB Indexes: 0MB Total: 0MB
wp_tla_rss_map Data: 0MB Indexes: 0MB Total: 0MB
wp_usermeta Data: 0MB Indexes: 0MB Total: 0MB
wp_users Data: 0MB Indexes: 0MB Total: 0MB
*** 12 Tables | Data: 8.78MB Indexes: .25MB Total: 9.03MB ***
With a few small modifications I'm sure it'd be possible to get the script working PostgreSQL or other RDBMSes. The full code is below:
#!/bin/bash
# Calculate the storage space used up by all tables in a given MySQL database
# Ben Dowling - www.coderholic.com
database=$1
username=$2
password=$3
if [ ${#database} -eq 0 ]
then
echo "Usage: $0 <database> [username [password]]"
exit
fi
if [ "$password" ]
then
password="-p$password"
fi
mysql="mysql -u $username $password $database"
$mysql -se "USE $database";
tables=$($mysql -se "SHOW TABLES")
totalData=0
totalIndex=0
totalTables=0
for table in $tables
do
output=$($mysql -se "SHOW TABLE STATUS LIKE \"$table\"\G")
data=$(echo "$output" | grep Data_length | awk -F': ' '{print $2}')
dataMegs=$(echo "scale=2;$data/1048576" | bc)
index=$(echo "$output" | grep Index_length | awk -F': ' '{print $2}')
indexMegs=$(echo "scale=2;$index/1048576" | bc)
total=$(($index+$data))
totalMegs=$(echo "scale=2;$total/1048576" | bc)
echo "$table Data: ${dataMegs}MB Indexes: ${indexMegs}MB Total: ${totalMegs}MB"
totalData=$(($totalData+$data))
totalIndex=$(($totalIndex+$index))
totalTables=$(($totalTables+1))
done
dataMegs=$(echo "scale=2;$totalData/1048576" | bc)
indexMegs=$(echo "scale=2;$totalIndex/1048576" | bc)
total=$(($totalIndex+$totalData))
totalMegs=$(echo "scale=2;$total/1048576" | bc)
echo "*** $totalTables Tables | Data: ${dataMegs}MB Indexes: ${indexMegs}MB Total: ${totalMegs}MB ***"