Query across all projects to list jar files

Document ID : KB000009267
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Need a query to produce a listing of all jar files in all repositories. Fields to be reported: project name, repo name, path to file and file name. Only need *.jar files.

Environment:
Windows
Cause:

SQL query request.

Resolution:

SQL:

 

SELECT DISTINCT HARENVIRONMENT.ENVIRONMENTNAME AS "PROJECT NAME",
HARREPOSITORY.REPOSITNAME AS "REPO NAME",
HARPATHFULLNAME.PATHFULLNAME AS "PATH TO FILE",
HARITEMNAME.ITEMNAME AS "FILE NAME"
FROM HARVERSIONS
INNER JOIN HARPATHFULLNAME
ON HARVERSIONS.PATHVERSIONID = HARPATHFULLNAME.VERSIONOBJID
INNER JOIN HARITEMNAME
ON HARITEMNAME.NAMEOBJID = HARVERSIONS.ITEMNAMEID
INNER JOIN HARVERSIONINVIEW
ON HARVERSIONS.VERSIONOBJID = HARVERSIONINVIEW.VERSIONOBJID
INNER JOIN HARVIEW
ON HARVIEW.VIEWOBJID = HARVERSIONINVIEW.VIEWOBJID
INNER JOIN HARREPINVIEW
ON HARVIEW.VIEWOBJID = HARREPINVIEW.VIEWOBJID
INNER JOIN HARREPOSITORY
ON HARREPOSITORY.REPOSITOBJID = HARREPINVIEW.REPOSITOBJID
INNER JOIN HARENVIRONMENT
ON HARENVIRONMENT.ENVOBJID = HARVIEW.ENVOBJID
WHERE
  HARENVIRONMENT.ENVIRONMENTNAME != ''
AND
HARITEMNAME.ITEMNAMEUPPER LIKE '%.JAR'

ORDER BY HARREPOSITORY.REPOSITNAME

Additional Information:

This SQL will work for Oracle and SQL Server databases and Harvest R12.1 and above.