How to find an existence of an item in entire database

Document ID : KB000097624
Last Modified Date : 22/05/2018
Show Technical Document Details
Introduction:
Is there a way to find an existence of a file/item in all the projects and list out the projects that has the file?
Background:
In SCM Workbench, you can only search for files one project and state at a time.  If you need a broader search, an SQL select statement that queries the entire database will make more sense.
Environment:
CA Harvest SCM all versions and platforms.
Oracle database
Instructions:
Here is a query that would work for that:
 
SELECT DISTINCT HARENVIRONMENT.ENVIRONMENTNAME,
   HARPACKAGE.PACKAGENAME,
   HARPATHFULLNAME.PATHFULLNAME,
   HARITEMNAME.ITEMNAME,
   HARVERSIONS.MAPPEDVERSION,
   HARVERSIONS.VERSIONSTATUS
 FROM HARENVIRONMENT
 INNER JOIN HARVIEW ON HARENVIRONMENT.ENVOBJID = HARVIEW.ENVOBJID
 INNER JOIN HARVERSIONINVIEW ON HARVIEW.VIEWOBJID = HARVERSIONINVIEW.VIEWOBJID
 INNER JOIN HARVERSIONS ON HARVERSIONS.VERSIONOBJID = HARVERSIONINVIEW.VERSIONOBJID
 INNER JOIN HARPACKAGE ON HARPACKAGE.PACKAGEOBJID = HARVERSIONS.PACKAGEOBJID
 INNER JOIN HARPATHFULLNAME ON HARVERSIONS.PATHVERSIONID = HARPATHFULLNAME.VERSIONOBJID
 INNER JOIN HARITEMNAME ON HARITEMNAME.NAMEOBJID = HARVERSIONS.ITEMNAMEID
 WHERE HARENVIRONMENT.ENVOBJID > 0
 AND UPPER(ITEMNAME) LIKE '%README.TXT%'   /* file name goes here, all upper case, the % sign is a wildcard */
/* AND UPPER(PATHFULLNAME) LIKE '%\SAMPLEREPOSITORY\DOCS%'  */  /* if you know the path name, you could uncomment this line and put it here */
 
I set this up to convert the file name to all upper case for comparison, so that you wouldn’t miss any results because of the wrong capitalization.  So the value you put in for comparison also must be upper case.
 
In Oracle, the wildcard character is the percent (%) sign, so you can use that to broaden the search if needed.