In SQL Server, you can use the sysmail_help_profile_sp stored procedure on the msdb database to retrieve a list of all Database Mail profiles.

You can also return profile info based on the profile name or ID.

Return All Profiles

To return all profiles, simply execute the stored procedure without any parameters.

EXEC msdb.dbo.sysmail_help_profile_sp;

Result:

+--------------+------------------+---------------------------+
| profile_id   | name             | description               |
|--------------+------------------+---------------------------|
| 1            | DB Admin Profile | Profile for admin emails. |
+--------------+------------------+---------------------------+

In my case, there’s just one profile. The name and description were previously provided by myself when I added this profile with the sysmail_add_profile_sp stored procedure.

Note that the sysmail_add_profile_sp stored procedure is in the msdb database and is owned by the dbo schema. Therefore, you’ll need to use three-part naming if msdb is not the current database.

Based on Profile Name

Alternatively, you can narrow it down to just a single profile by passing the profile name or ID as a parameter.

Here’s how to pass the profile name:

EXEC msdb.dbo.sysmail_help_profile_sp
    @profile_name = 'DB Admin Profile';

Based on Profile ID

And here’s how to pass the profile ID:

EXEC msdb.dbo.sysmail_help_profile_sp
    @profile_id = 1;

Note that @profile_id is provided as an int.