create procedure sendmail
with ENCRYPTION
as
/*********************
发送邮件到指定信箱
邮件内容：数据库硬盘信息数据库备份完整性
boyi55   2007-11-02
  *********************/
begin
--用xml生成HTML文本
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1><font color=red>数据库硬盘可用空间</font></H1>' +
    N'<table border="1"  bordercolor=silver bgcolor=Silver cellspacing="0" cellpadding="0">' +
    N'<tr><th>检查时间</th><th>服务器IP</th>' +
    N'<th>硬盘分区</th><th>可用空间(G)</th>' +
       CAST ( ( SELECT
/*
下面这一句话中的'background-color:red;' as "@style"表示将style属性写入到标签内部，即<tr style=background-color:red;>如果要放在下级标签即<td>里面，可以把"@style"换成 "td/@style"
用case做判断，如果硬盘空间小于5G将用红色提示，否则用绿色
 */
                   case when disk_size<5.0 then 'background-color:red;' else 'background-color:green' end as "@style",

                    td = convert(varchar(20),createdate,23),'',
                    td = IP,'',
                    td = disk_partition, '',
                    td = disk_size,''
              FROM check_disk
              WHERE convert(varchar(20),createdate,23)=convert(varchar(20),getdate(),23)
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>';
 

declare @substr varchar(100)
set @substr=convert(varchar(20),getdate(),23)+' 数据库服务器相关检查结果'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='boyi55',
 @recipients='boyi55@hotmail.com',
    @subject = @substr,
    @body = @tableHTML,
    @body_format = 'HTML' ;
end