Check all database email profile in SQL Server by using sysmail_profile

Check all database email profile in SQL Server by using sysmail_profile

I used to manage many different SQL Server Instances so I need some scripts to complete my work quickly with efficiency. Recently I had a requirement to check all email profile whether it is able to send an email or not. Well, that SQL Server instance was having approx 18 different database mail profile and send test email from each profile manually is time consuming and there is a small chance to miss any profile while manual testing so rather than choosing manual way, I have developed following TSQL script quickly which get all email profile from MSDB database (BTW, email profile is being stored in MSDB database only) and send an email from every email profile one by one in loop.

I have used “sysmail_profile” system table of MSDB database which stores information about email profile in SQL Server. Here is the script I have developed:

[sourcecode language=”sql”]USE msdb
GO

DECLARE
@Total int
,@cnt int
,@ProfileName varchar(50)
,@Server varchar(50)
,@Sub varchar(400)

SET @Total = (SELECT MAX(profile_id) FROM sysmail_profile)
SET @cnt = 1

SET @Server = @@SERVERNAME

WHILE(@cnt <= @Total)
BEGIN
SET @ProfileName = (select name from sysmail_profile where profile_id = @cnt)

SET @Sub = ‘Testing Email From ‘ + @Server + ‘ from profile : ‘ + @ProfileName
IF (@ProfileName is not null)
BEGIN

PRINT @ProfileName
PRINT @Sub

EXEC msdb.dbo.sp_send_dbmail
  @recipients = ‘Test@gmail.com’
 , @subject = @Sub
 , @body = @Sub
 , @profile_name = @ProfileName
, @body_format = ‘HTML’ ;
END

SET @cnt = @cnt + 1
END[/sourcecode]

As soon as you execute above script, it will send you an email from each database email profile from your SQL Server instance.

Please note that if you don’t have any email profile set in your SQL Server Instance, you won’t get anything.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Check all database email profile in SQL Server by using sysmail_profile”

Comments are closed.