#!/bin/bash
###############
# DESCRIPTION: list database tables and their size in MB
# VARIABLES:
#   DB(char): only show tables from database $DB
#   LIMIT(int): only show the biggest $LIMIT number of tables
###############

[[ $DB =~ ^[^\\/?%*:|\"\<\>.]{1,64}$ ]] \
  && filter_db="WHERE \`table_schema\` = '$DB'" \
  || filter_db=""
[[ $LIMIT =~ ^[0-9]+$ ]] \
  && result_limit="LIMIT $LIMIT" \
  || result_limit=""

query="
SELECT
	table_schema as 'DATABASE',
	table_name AS 'TABLE',
	ROUND(((data_length + index_length) / 1024 / 1024), 2) 'SIZE (MB)'
FROM information_schema.TABLES
$filter_db
ORDER BY \`SIZE (MB)\` DESC
$result_limit;"
  
mysql -te "$query"