If you are Performing package cleanup,this query might help you to Identify how many packages with no advertisements(standard advertisement and Task Sequence)
so you know What action to Take next!
select v_Package.PackageID, v_Package.Name,v_Package.SourceVersion,v_Package.SourceDate
from dbo.v_package
Where
packageID not in (select PackageID from dbo.v_Advertisement)and
PackageID not in (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo) and
v_Package.name not like '%osd%' and V_package.PackageType ='0'group by v_Package.PackageID, v_Package.Name,v_Package.SourceVersion,v_Package.SourceDate
order by v_Package.PackageID
If you want to know other packages like OSD,Driver,Software update packages,you can replace Package type with below Values :
When 3 Then ‘Driver Package’
When 4 Then ‘Task Sequence Package’
When 5 Then ‘software Update Package’
When 6 Then ‘Device Settings Package’
When 7 Then ‘Virtual Package’
When 257 Then ‘Image Package’
When 258 Then ‘Boot Image Package’
When 259 Then ‘OS Install Package’