How to modify the below script to take tables and its sizes for a particular databse to send via mail to a recipient? SELECT top 10
    t.NAME AS TableName,
    (SUM(a.total_pages) * 8)/1024.0/1024.0 AS TotalSpaceGB
    FROM sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID &gt; 255 
GROUP BY 
    t.Name, s.Name
ORDER BY 
    TotalSpaceGB desc
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 !!!