Pages

Monday, June 27, 2011

MYSQL : Listing summary of all Instance Schemas including disk size

MYSQL : Listing summary of all Instance Schemas including disk size


#setting session variable for storing schemaname


SET @schemaname = IFNULL(@schemaname,DATABASE());


#one row schema usage 


SELECT   table_schema,
         SUM(data_length+index_length)/1024/1024 AS total_mb,
         SUM(data_length)/1024/1024 AS data_mb,
         SUM(index_length)/1024/1024 AS index_mb,
         COUNT(*) AS tables,
         CURDATE() AS today
FROM     information_schema.tables
WHERE    table_schema=@schemaname
GROUP BY table_schema;


#Display Engine/Collation Summary


SELECT   table_schema,engine,table_collation,
         COUNT(*) AS tables
FROM     information_schema.tables
WHERE    table_schema=@schemaname
GROUP BY table_schema,engine,table_collation;




#Display Schema Table Usage for each schema


SELECT @schemaname as table_schema, CURDATE() AS today;
SELECT   if(length(table_name)>20,concat(left(table_name,18),'..'),table_name) AS table_name,
         engine,row_format as format, table_rows, avg_row_length as avg_row,
         round((data_length+index_length)/1024/1024,2) as total_mb, 
         round((data_length)/1024/1024,2) as data_mb, 
         round((index_length)/1024/1024,2) as index_mb
FROM     information_schema.tables 
WHERE    table_schema=@schemaname
ORDER BY 6 DESC;