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