ā11-17-2011 05:19 PM
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":
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
ā11-17-2011 05:19 PM
Just add a line to the "where" clause to specifiy which service(s) you want to run this for rather than for the whole catalog:
--By Service Name
ds.Name = "My Service Name"
--By Service ID (Multiple services can be returned.)
ds.ServiceID IN ('1', '2', '3')
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide