How to query for processes that use a particular Mail Template?

Question

I have set up a bunch of Process Chains that do various things, one of which is sending rich mails. I now need to make a minor adjustment to all processes that reference a particular Mail Template. I could click through each Rich Mail Process step to find them but there are alot of them. I looked in the tables to see where the process step parameters may be held so I can query the database to see which process steps I need to update but I couldn't find a table that stored that information. Are process step parameters stored in a table somewhere?

Christian Stevens · Accepted Answer

You might be hard out of luck, because those MailID parameters of the SendRichMailSteps (table is JobRunParameter btw.) can be evaluated in various ways, e.g. through values() assigned in pre-scripts of JobChains, Jobs etc. . 
Also, they can be referenced there in various ways, by naming Ident_DialogRichMail, but using UID_DialogRichMail, or any other thinkable way is possible ("the template associated with that approval step" is also a fine example). 
 
Your safest bet, if you don't know where the template is used, is probably to analyse every single SendRichMail Job manually. 
If you have an extensive enough JobHistory and the processes are actually in use, you could also try searching the parameters there (ParamIn) for the UID_DialogRichmail. 
 
Good luck and let us know if you find a better way.

Markus Weiss-Ehlers · Answer

Christain is right about the fact that there is often no direct relation between the mail template and the process. 
 But you can use the following SQL to identify all MAILID parameters including the content of the value template. 
 SELECT jc.Name, j.Name, jrp.UID_JobParameter, jrp.ValueTemplate FROM dbo.JobRunParameter jrp	
JOIN Job j ON jrp.UID_Job = j.UID_Job
JOIN dbo.JobChain jc ON j.UID_JobChain = jc.UID_JobChain	
JOIN dbo.JobTask jt	ON jt.UID_JobTask = j.UID_JobTask 
WHERE jt.TaskName = 'SendRichMail' and jrp.Name ='MailId'

Endpoint Privilege Management

How to query for processes that use a particular Mail Template?