View SQL Server Mail Items

Sometimes you need to investigate emails being sent from SQL Server. This query the last 100 items.

/*******
* Title:	View Last Mail Items
* Date:		2018-07-01
* Author:	Joe McDermott
********/

SELECT TOP(100) 
	 M.mailitem_id			AS MailItemId
	,M.profile_id			AS ProfileId
	,P.[name]				AS ProfileName
	,M.send_request_date	AS DateSent
	,M.send_request_user	AS [User]
	,M.sent_status			AS SentStatus
	,CASE 
		WHEN M.sent_status = 'failed' 
		THEN 1 
		ELSE 0 
	 END AS IsFailed
	,M.body_format			AS BodyFormat
	,'To: ' + M.recipients
		+ ISNULL('CC: ' + M.copy_recipients,'')
			+ ISNULL('BCC: ' + M.blind_copy_recipients,'') AS Recipients
	,M.[subject]			AS [Subject]
	,M.body					AS Body
	,CASE 
		WHEN M.file_attachments IS NOT NULL 
		THEN 1 
		ELSE 0 
	 END					AS IsAttachment
FROM msdb.dbo.sysmail_allitems AS M
	INNER JOIN msdb.dbo.sysmail_profile AS P ON M.profile_id = P.profile_id
ORDER BY 1 DESC;