Find all workflows that email particular address

Quite often people within organisation change roles or leave. And if your HR.Net workflows were programmed to email that person, you’ll have to go into these workflows and change the address. But if you have thousands of workflows? you don’t want to go through each one and check if it emails that particular address. For these cases you should use this query:

select 
	pppf.folder_name as [Folder],
	ppf.folder_name as [Folder],
	pf.folder_name as [Folder],
	f.folder_name as [Folder],
	flow.workflowname,
	flow.description,
	flow.allowstart,
	params.WORKFLOWID,
	params.STAGEID,
	params.DATA 
from OC_WORKFLOWSTAGEPARAMETERS params
  inner join OC_WORKFLOWS flow on params.workflowid = flow.workflowid
  left join OC_ADMINCONSOLE_FOLDERS f on flow.folderid = f.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where params.DATA = 'joe.doe@example.com' and params.datatype=0
order by pppf.folder_name, ppf.folder_name, pf.folder_name, f.folder_name, flow.workflowname

This will show you all the workflows (including folder structure) and stage names that email to joe.doe@example.com.
To mass-update the email address you need to update OC_WORKFLOWSTAGEPARAMETERS table. In particular Data field.
In that table, as you may have guessed by the name of it, there are parameters for different workflow stages, including Mailers, Mail-Merges and Send-Form.

To change the address from Joe.Doe to John.Smith, you need to execute this simple query:

update OC_WORKFLOWSTAGEPARAMETERS
set DATA ='joe.doe@example.com'
where datatype=0 and DATA = 'john.smith@example.com'

And that’s all there is to it!