How can can I determine where email templates are used?
Here is a SQL query which was recently provided in response to a YES! question.
1. E-mails attached to the "Notify when plan cancelled":
select
da.Name as ServiceGroupName,
ds.Name as ServiceName,
dp.Subject as MonitorTaskName,
de.LogicName as Event,
demt.name as EmailTemplateName
from
defevent de,
defemailtemplate demt,
defeventtrigger det,
defproject dp,
defservice ds,
defarea da
where
det.objectid = 21 and
det.objectinstid = dp.projectid and
de.eventid = det.eventid and
demt.emailtemplateid = det.templateobjectinstid and
ds.serviceid = dp.ownerinstid and
da.areaid = ds.areaid
2. E-mails attached to all delivery plan tasks:
select
da.Name as ServiceGroupName,
ds.Name as ServiceName,
dt.Name as TaskName,
de.LogicName as Event,
demt.name as EmailTemplateName
from
defevent de,
defemailtemplate demt,
defeventtrigger det,
deftask dt,
defproject dp,
defservice ds,
defarea da
where
det.objectid = 46 and
det.objectinstid = dt.taskid and
de.eventid = det.eventid and
demt.emailtemplateid = det.templateobjectinstid and
dp.projectid = dt.projectid and
ds.serviceid = dp.ownerinstid and
da.areaid = ds.areaid
3. E-mails attached to all approval tasks:
select
da.Name as ServiceGroupName,
ds.Name as ServiceName,
dwsr.Subject as ApprovalReviewStep,
de.LogicName as Event,
demt.name as EmailTemplateName
from
defevent de,
defemailtemplate demt,
defeventtrigger det,
defworkflowsteproles dwsr,
defservice ds,
defarea da
where
det.objectid = 57 and
det.objectinstid = dwsr.steproleid and
de.eventid = det.eventid and
demt.emailtemplateid = det.templateobjectinstid and
ds.serviceid = dwsr.ownerid and
da.areaid = ds.areaid