DECLARE @Document NVARCHAR(MAX)
, @DocumentHead NVARCHAR(MAX)
, @DocumentTail NVARCHAR(MAX)
, @TableHead NVARCHAR(MAX)
, @TableBody NVARCHAR(MAX)
, @TableTail NVARCHAR(MAX)
SET @DocumentHead = N'<html><head>'
+ N'<style>'
+ N'th, td'
+ N'{ '
+ N' border: solid black 1px;'
+ N' padding: 1px 5px 1px 5px;'
+ N' font-size: 11pt;'
+ N'}'
+ N'</style>'
+ N'</head>'
+ N'<body>'
+ N'<h3>'
+ N'Disk Space Available on SQL1 SQL Server'
+ N'</h3>';
SET @DocumentTail = '</body></html>';
SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'
+ N'<tr bgcolor=#C0C0C0>'
+ N' <th>Disk Name</th>'
+ N' <th>Available Space in GB </th>'
+ N'</tr>';
SET @TableTail = '</table>';
CREATE TABLE #tbldiskSpace
(
driveName VARCHAR(1000),
freeSpace FLOAT
)
INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives
SELECT @TableBody = (SELECT ROW_NUMBER() Over(Order By T1.driveName) % 2 As [TRRow]
,T1.driveName AS [TD],
CONVERT(VARCHAR(MAX), Round((freespace)/1024, 1))
FROM #tbldiskSpace T1 ORDER BY drivename
FOR XML RAW('tr'), ELEMENTS);
IF @TableBody IS NULL
BEGIN
PRINT 'N/A';
RETURN;
END
ELSE
BEGIN
-- Replace the entity codes and row numbers
SET @TableBody = Replace(@TableBody, '_x0020_', space(1))
SET @TableBody = Replace(@TableBody, '_x003D_', '=')
SET @TableBody = Replace(@TableBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @TableBody = Replace(@TableBody, '<TRRow>0</TRRow>', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default'
, @recipients= 'naresh.koudagani@ABC.com'
, @subject = 'Disk Space Available on SQL SERVER'
, @body = @Document
, @body_format = 'HTML';
END
DROP TABLE #tbldiskSpace
No comments:
Post a Comment