< Blog

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