Hi friends, today in SQL Server system stored procedure series we will learn how to get information about database mail profile using stored procedure sysmail_help_profile_sp.
Sysmail_help_profile_sp stored procedure provides information about the database mail profiles. This stored procedure is stored in msdb database and owned by dbo schema. The procedure must be executed with a three part name if current database is not msdb.
Syntax:
sysmail_help_profile_sp [ [ @profile_id = ] profile_id | [ @profile_name = ] 'profile_name' ]
Arguments:
@profile_id is the id of database mail profile with default value NULL.
@profile_name is the name of the database mail profile.
When profile_id or profile_name is specified, sysmail_help_profile_sp stored procedure return information about that profile. If a profile_id or profile_name is not specified it returns information about all profiles in current SQL Server instance.
In our previous blog we created a database mail profile with name ‘Database Test Mail’ here. Now we will pass that profile name in the parameter to retrieve information about that profile.
EXEC msdb.dbo.sysmail_help_profile_sp@profile_name = 'Database Test Mail'
Here, in result set we get information of that database mail profile.
Profile_id – Profile id of database mail profile.
Name – Name of database mail profile.
Description – Description about database mail profile.
That’s all folks for the day. In next blog we will see how to update the information about database mail profile. Have a nice day 🙂
