View Database Mail Configuration in SQL Server with T-SQL

While the SQL Server Management Studio (SSMS) offers a graphical interface for configuring Database Mail, T-SQL provides detailed insights into the configuration through various system stored procedures and views. Here's how you can leverage them:

The provided T-SQL utilize the following stored procedures:

EXEC msdb.dbo.sysmail_help_configure_sp

This procedure displays information about the overall Database Mail configuration settings.

EXEC msdb.dbo.sysmail_help_account_sp;

 This procedure provides details about configured Database Mail accounts, including server details, authentication methods, and more.

EXEC msdb.dbo.sysmail_help_profile_sp;

 This procedure reveals information about Database Mail profiles, which define how emails are formatted and delivered.

EXEC msdb.dbo.sysmail_help_profileaccount_sp

This procedure shows the relationships between profiles and accounts, indicating which profiles use which accounts for sending emails.

Execute all of the stored procedures simultaneously to view the data simultaneously.  

Copied!

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

We can also retrieve this data using the following SELECT statement. 

Copied!

SELECT *
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
INNER JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id