How to modify the below script to take tables and its sizes for a particular databse to send via mail to a recipient?
My guess is the query in your question looks like PostgreSQL information_schema. The current database is assumed when you connect to it.

I believe you want the MySQL equilavent

Here is my suggestion for the Top 10 tables in the Current Database.

SET @lineitem = 0;
SET @topitem = 10;
SELECT line,tbl,units,dat,ndx,siz FROM
(SELECT
    (@lineitem:=@lineitem+1) line,tbl,
    CONCAT(SUBSTR('KMGTP',ex,1),'B') units,
    FORMAT(dt/POWER(1024,ex),2) dat,
    FORMAT(nx/POWER(1024,ex),2) ndx,
    FORMAT(sz/POWER(1024,ex),2) siz
FROM (SELECT *,FLOOR(GREATEST(1,
    LOG(GREATEST(dt,1))/LOG(1024),
    LOG(GREATEST(nx,1))/LOG(1024),
    LOG(GREATEST(sz,1))/LOG(1024))) ex FROM
(SELECT table_name tbl,data_length dt,
index_length nx,data_length+index_length sz
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY sz DESC) AAA) AA) A
WHERE line &lt;= @topitem;


If you have a specific database in mind, like mydb, you would changed the subquery WHERE clause

SET @lineitem = 0;
SET @topitem = 10;
SELECT line,tbl,units,dat,ndx,siz FROM
(SELECT
    (@lineitem:=@lineitem+1) line,tbl,
    CONCAT(SUBSTR('KMGTP',ex,1),'B') units,
    FORMAT(dt/POWER(1024,ex),2) dat,
    FORMAT(nx/POWER(1024,ex),2) ndx,
    FORMAT(sz/POWER(1024,ex),2) siz
FROM (SELECT *,FLOOR(GREATEST(1,
    LOG(GREATEST(dt,1))/LOG(1024),
    LOG(GREATEST(nx,1))/LOG(1024),
    LOG(GREATEST(sz,1))/LOG(1024))) ex FROM
(SELECT table_name tbl,data_length dt,
index_length nx,data_length+index_length sz
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY sz DESC) AAA) AA) A
WHERE line &lt;= @topitem;


Give it a Try !!!