SCCM Unused Packages

This is a t-sql query to help find Packages which you are no longer using so they can be properly organized/categorized/deleted.

Thanks to John Nelson (Number 2) and Christjan Schumann for doing the heavy lifting.  I only put the two puzzle pieces together.

DECLARE @ObjectTypes TABLE (ObjectType INT PRIMARY KEY, TypeDescription VARCHAR(46));
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(2,'Package');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(3,'Advertisement');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(7,'Query');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(8,'Report');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(9,'MeteredProductRule');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(11,'ConfigurationItem');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(14,'OperatingSystemInstallPackage');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(17,'StateMigration');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(18,'ImagePackage');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(19,'BootImagePackage');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(20,'TaskSequencePackage');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(21,'DeviceSettingPackage');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(23,'DriverPackage');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(25,'Driver');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(1011,'SoftwareUpdate');
INSERT INTO @ObjectTypes (ObjectType,TypeDescription) VALUES(2011,'ConfigurationItem (Configuration baseline)');

WITH fldr AS (
    --top level folders (anchor)
    SELECT     CAST('\'+f.Name AS VARCHAR(512)) AS Folder, f.ContainerNodeID AS ID, f.ParentContainerNodeID AS ParentID,
    ot.ObjectType, ot.TypeDescription
    FROM dbo.vSMS_Folders f
    JOIN @ObjectTypes ot ON f.ObjectType = ot.ObjectType
    WHERE f.ParentContainerNodeID = 0
    UNION ALL
    --child folders (recursive)
    SELECT
    CAST(Parent.Folder+'\'+Child.Name AS VARCHAR(512)) AS Folder, Child.ContainerNodeID AS ID,
    Child.ParentContainerNodeID AS ParentID, ot.ObjectType, ot.TypeDescription
    FROM dbo.vSMS_Folders Child
    JOIN @ObjectTypes ot ON Child.ObjectType = ot.ObjectType
    JOIN fldr AS Parent ON Child.ParentContainerNodeID = Parent.ID AND Child.ObjectType = Parent.ObjectType
)

SELECT F.TypeDescription [Node], F.Folder, p.PackageID, p.Manufacturer, p.Name, Version, p.Language, p.Description,
PkgSourcePath AS [Source Path], LastRefreshTime,
(SELECT COUNT(PkgID) FROM vPkgStatusSummaryDistPts WHERE PkgID = p.PackageID) AS [No of DPs]
FROM v_Package p
left join vFolderMembers M on M.InstanceKey = P.PackageID
left join fldr F on F.ID = M.ContainerNodeID
WHERE p.PackageType = 0
AND p.PackageID NOT IN (SELECT PackageID FROM v_Advertisement)
AND p.PackageID NOT IN (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo)
AND F.Folder NOT Like '\Archive%'
ORDER BY F.TypeDescription, F.Folder, p.Manufacturer, p.Name

References:

• http://myitforum.com/myitforumwp/2011/11/03/configmgr-query-to-find-all-cm-folders-and-show-where-they-are-in-the-console/
• http://myitforum.com/cs2/blogs/jnelson/archive/2009/10/22/142315.aspx
• http://blog.itminutes.net/?p=781