DECLARE @cmd Varchar (1000),@profile_name Varchar(1000)
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare @Disk_Size Table
(Drive Nvarchar(Max),Free_Space_MB decimal(30,2))
Insert Into @Disk_Size
Exec Xp_fixeddrives
--Select * from @Disk_Size
SET @xml = CAST(( SELECT DS.Drive As 'td',' ',DS.Free_Space_MB AS 'td' ,' ', convert(varchar(20),getdate(),120) as 'td' from @Disk_Size DS
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Current Disk Free Space </H3>
<table border = 1>
<th> Drive Name </th><td bgcolor=#F0E68C><b>Free_Space_MB (%)<b> </td> <th>Capture_Date</th></tr>'
--<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th><th> FreeSpace(%) </th> <th>Capture_Date</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
Set @cmd= @@SERVERNAME + ': Current Disk Free Space'
SELECT Top 1 @profile_name=name FROM msdb.dbo.sysmail_profile
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name, -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'IT.ESA-DBA@sqldbanow.com', -- replace with your email address
@subject = @cmd
No comments:
Post a Comment