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.

Email

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