Is there any way to search for a particular UDP across many projects?

CA Harvest SCM all versions and platforms

Here is an SQL query that will let you string-search the “Program Name” field for all UDPs in the database: 

SELECT HARUDP.PROCESSOBJID, 
HARUDP.PROCESSNAME, 
PARENTPROJECT.ENVIRONMENTNAME AS PARENTPROJECTNAME, 
PARENTSTATE.STATENAME AS PARENTSTATENAME, 
PARENTPROCESS.PROCESSNAME AS PARENTPROCESSNAME, 
HARLINKEDPROCESS.PROCESSPRELINK, 
HARENVIRONMENT.ENVIRONMENTNAME AS PROJECTNAME, 
HARSTATE.STATENAME, 
HARUDP.PROGRAMNAME 
FROM HARUDP 
LEFT JOIN HARLINKEDPROCESS ON HARLINKEDPROCESS.PARENTPROCOBJID = HARUDP.PARENTPROCOBJID 
AND HARLINKEDPROCESS.PROCESSOBJID = HARUDP.PROCESSOBJID 
LEFT JOIN HARSTATEPROCESS PARENTPROCESS ON PARENTPROCESS.STATEOBJID = HARLINKEDPROCESS.STATEOBJID 
AND PARENTPROCESS.PROCESSOBJID = HARLINKEDPROCESS.PARENTPROCOBJID 
LEFT JOIN HARSTATE PARENTSTATE ON PARENTSTATE.STATEOBJID = PARENTPROCESS.STATEOBJID 
LEFT JOIN HARSTATE ON HARUDP.STATEOBJID = HARSTATE.STATEOBJID 
LEFT JOIN HARENVIRONMENT ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID 
LEFT JOIN HARENVIRONMENT PARENTPROJECT ON PARENTPROJECT.ENVOBJID = PARENTSTATE.ENVOBJID 
WHERE HARUDP.PROGRAMNAME LIKE '%perl%' /* <--- Put the string you’re searching for between the percent signs */ 

The result will show UDPs that are “stand-alone” and UDPs that are pre-linked or post-linked to another process. So either ParentProjectName, ParentStateName and ParentProcessName will be filled in for the pre-link/post-links, or ProjectName and StateName will be filled in for the stand-alone UDPs. 

Put the string you’re searching for between the percent signs. It is a case sensitive search. If you want to make it case Insensitive, change the last line to the following and make your search string all-caps: 

WHERE UPPER(HARUDP.PROGRAMNAME) LIKE '%PERL%' /* <--- Put the string you’re searching for between the percent signs */