Number of databases and it's overall size
Ravi Sekhara Reddy . G
Database Specialist | SQL Server DBA | PostgreSQL DBA | Power BI | Cloud Consultant | Expert in SQL Server Administration | Database Migration | PowerShell | Azure & AWS Cloud Solutions 🌟
--No of databases and it's Overall Size
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT count(DISTINCT d.name) AS 'td','',
(SUM(mf.size) * 8 / 1024) AS 'td',''
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Total No of databases and it''s'' Overall Size</H3>
<table border = 1>
<tr bgcolor="#807393">
<th> No_Of_Databases </th> <th> Size_MB </th> </tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_Name', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'recipient's_email', -- replace with your email address
@subject = 'No of databases and its overall size' ;