Background
The first of each of month is when checks and reports go out.
Thankfully I had cc myself as a recipient of a new report we have scheduled.
I received an email and was wondering why I, and not the compliance team is the main recipient.
Here is the email’s envelope
Metadata
Let us see which tables can help track outgoing emails…
dbo.sp_ListMailItems
Code
USE [master] GO if object_id('dbo.sp_ListMailItems') is null begin exec('create procedure [dbo].[sp_ListMailItems] as ') end go alter procedure [dbo].[sp_ListMailItems] ( @maxNumberofEntries bigint = 100 ) as begin set nocount on; set XACT_ABORT on; set transaction isolation level read uncommitted; SELECT TOP (@maxNumberofEntries) --tblSMI.[mailitem_id] --, tblSMI.[profile_id] [recipientsTo] = tblSMI.[recipients] , [recipientsCC] = tblSMI.[copy_recipients] , [recipientsBCC] = tblSMI.[blind_copy_recipients] , tblSMI.[subject] --, tblSMI.[from_address] -- , tblSMI.[reply_to] , [body] = tblSMI.[body] , [bodyFormat] = tblSMI.[body_format] --, tblSMI.[importance] --, tblSMI.[sensitivity] , [fileAttachment] = tblSMI.[file_attachments] , [attachment] = tblSMI.[attachment_encoding] , tblSMI.[query] , [executeQueryDatabase] = tblSMI.[execute_query_database] , [atatchQueryResultAsFile] = tblSMI.[attach_query_result_as_file] , [queryResultHeader] = tblSMI.[query_result_header] , [queryResultWidth] = tblSMI.[query_result_width] --, tblSMI.[query_result_separator] --, tblSMI.[exclude_query_output] , [appendQueryError] = case tblSMI.[append_query_error] when 1 then 'Yes' else 'No' end --, tblSMI.[send_request_date] , [sendRequestUser] = tblSMI.[send_request_user] -- ,[sent_account_id] , [sendStatus] = case tblSMI.[sent_status] when 1 then 'Yes' else 'No' end , [dateSent] = tblSMI.[sent_date] --,[last_mod_date] --,tblSMI.[last_mod_user] , mailProfile = tblSMP.[name] , mailAccount = tblSMA.[name] FROM [msdb].[dbo].[sysmail_mailitems] tblSMI LEFT OUTER JOIN [msdb].[dbo].[sysmail_profile] tblSMP on tblSMI.[profile_id] = tblSMP.[profile_id] LEFT OUTER JOIN [msdb].[dbo].[sysmail_account] tblSMA on tblSMI.[sent_account_id] = tblSMA.account_id order by tblSMI.[mailitem_id] desc end go
Invocation
exec [dbo].[sp_ListMailItems]
Output
Source Code Version Control
GitHub
DanielAdeniji/SQLServerDatabaseMailMetadata
Link