This is a SQL query which will give you a detailed report of all distributed packages on a SCCM distribution point which includes information about Package Size, PackageID, Name, Description etc. This is a very helpful query if you want to do some clean up on your distribution point and looking for some package size information report.
Just change below the FQDN of Distribution point and Site Code.
select all SMS_DPContentInfo.Description,SMS_DPContentInfo.IsPredefinedPackage,SMS_DPContentInfo.NALPath,SMS_DPContentInfo.Name,SMS_DPContentInfo.ObjectID,SMS_DPContentInfo.ObjectType,SMS_DPContentInfo.ObjectTypeID,SMS_DPContentInfo.PackageID,SMS_DPContentInfo.SourceSize from fn_ListDPContents(1033) AS SMS_DPContentInfo where SMS_DPContentInfo.NALPath = N'["Display=\\FQDN of Distribution Point\"]MSWNET:["SMS_SITE=SiteCode"]\\FQDN of Distribution Point\'
Just change below the FQDN of Distribution point and Site Code.
select all SMS_DPContentInfo.Description,SMS_DPContentInfo.IsPredefinedPackage,SMS_DPContentInfo.NALPath,SMS_DPContentInfo.Name,SMS_DPContentInfo.ObjectID,SMS_DPContentInfo.ObjectType,SMS_DPContentInfo.ObjectTypeID,SMS_DPContentInfo.PackageID,SMS_DPContentInfo.SourceSize from fn_ListDPContents(1033) AS SMS_DPContentInfo where SMS_DPContentInfo.NALPath = N'["Display=\\FQDN of Distribution Point\"]MSWNET:["SMS_SITE=SiteCode"]\\FQDN of Distribution Point\'
