I was tasked to replace all the db mail profile name to a new profile name in all the objects in a database that uses specific old profile name. This can easily turn into a tedious task to manually open/edit all the sql server jobjects in each database.
I wrote this query to quickly find all the sql objects that use the specific db mail profile name. This will give you are list of all the sql objects in the Results window. Now you simply have to go by this list to edit/update each of these specific stored procs with the new db mail profile name.
SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE '%@profile_name%'
--AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id) Hope this helps.