Depended Package

Use this query to get the package name for any depended program.

See the TechNet forum post for full details.

SELECT
  Pkg.Name AS 'Package Name',
  ADV.ProgramName AS 'Program Name',
  ADV.AdvertisementName AS 'Advertisement Name',
  Col.Name AS 'Collection Name',
  CRQ.RuleName AS 'Security Group Name',
  Pgm.PackageID,
  Pgm.ProgramName,
  Pgm.DependentProgram,
  pkgsub.Name

FROM
  dbo.v_Advertisement Adv
  INNER JOIN dbo.v_Collection Col ON ADV.CollectionID = Col.CollectionID
  INNER JOIN dbo.v_Package Pkg ON ADV.PackageID = Pkg.PackageID
  INNER JOIN dbo.v_CollectionRuleQuery CRQ ON Col.CollectionID = CRQ.CollectionID
  INNER JOIN dbo.v_Program Pgm ON Pkg.PackageID = Pgm.PackageID
  left outer Join (select substring(Pgm.DependentProgram,0,9) as Subpac, PackageID from dbo.v_Program  Pgm where Pgm.DependentProgram != '') sub ON sub.PackageID = Pkg.PackageID
  left outer JOIN dbo.v_Package Pkgsub ON sub.Subpac = Pkgsub.PackageID
--where 
--  ADV.AdvertisementName like 'Z7_%' 
ORDER BY
  ADV.AdvertisementName

Leave a Comment