The database mail configuration saved into a temp table

This is actually super easy, and you don't even need to go through the trouble of doing INSERT... EXEC with the stored proc.

USE msdb

EXEC sp_helptext 'sysmail_help_account_sp'

This will return the script info:

CREATE PROCEDURE dbo.sysmail_help_account_sp  
   @account_id int = NULL,  
   @account_name sysname = NULL  
AS  
   SET NOCOUNT ON  

   DECLARE @rc int  
   DECLARE @accountid int  
   exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 1, 0, @accountid OUTPUT  
   IF @rc <> 0  
      RETURN(1)  

   IF (@accountid IS NOT NULL)  
      SELECT a.account_id, a.name, a.description, a.email_address, a.display_name, a.replyto_address, s.servertype, s.servername, s.port, s.username, s.use_default_credentials, s.enable_ssl   
      FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s  
      WHERE a.account_id = s.account_id AND a.account_id = @accountid  

   ELSE  
      SELECT a.account_id, a.name, a.description, a.email_address, a.display_name, a.replyto_address, s.servertype, s.servername, s.port, s.username, s.use_default_credentials, s.enable_ssl  
      FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s  
      WHERE a.account_id = s.account_id  

   RETURN(0)  

You can see that it just queries these system views in msdb:

SELECT *
FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s  
WHERE a.account_id = s.account_id

Which should be easy to integrate into your process.

Hope this helps!


@Erik Darling has a great method of extracting the account name from the msdb table directly. Use that method.

However, to help you understand how you could use the INSERT INTO ... EXEC construct, I've written this little snippet of code:

DECLARE @Command nvarchar(max);
DECLARE @params nvarchar(max);
DECLARE @ProfileName sysname;
DECLARE @ColumnList nvarchar(max);
DECLARE @tsql nvarchar(max);

SET @params = '@ProfileName sysname OUTPUT';

SET @tsql = N'EXEC msdb.dbo.sysmail_help_account_sp;'

SET @Command = N'CREATE TABLE #sysmailaccount
(
    <COLUMNLIST>
);';

SET @ColumnList = '';
SET @ColumnList = STUFF((SELECT ', ' + QUOTENAME(rs.name) + ' ' + rs.system_type_name
FROM sys.dm_exec_describe_first_result_set(@tsql, NULL, NULL) rs
ORDER BY rs.column_ordinal
FOR XML PATH('')), 1, 2, '');

SET @Command = REPLACE(@Command, N'<COLUMNLIST>', @ColumnList);

SET @Command = @Command + N'
INSERT INTO #sysmailaccount
' + @tsql + N';
SELECT @ProfileName = sma.name
FROM #sysmailaccount sma;
';
EXEC sys.sp_executesql @Command, @params, @ProfileName = @ProfileName OUT;
SELECT ProfileName = @ProfileName;

At the end of above code, you have @ProfileName containing the name of the first database mail profile name.

The code uses the sys.dm_exec_describe_first_result_set system function to construct a temporary table with the same definition as the output of msdb.dbo.sysmail_help_account_sp. It then uses sys.sp_executesql to execute an INSERT INTO ... EXEC command to actually run the sysmail_help_account_sp stored proc, placing the output into the #sysmailaccount temp table.