The following SQL query display all pending content distribution to all distribution points. This will display all packages that are not currently in state 0.
/* All Content in Flight to All DPs */
select p.pkgid as 'PKGID', s.installstatus as 'Content Status', p.name as 'Content Name',(p.SourceSize/1024) as 'Content Size(MB)', s.state as 'Content State', Count(substring(servername,0,charindex('.',servername))) as 'Count'
from v_PackageStatusDistPointsSumm s
join smspackages p on s.packageid = p.pkgid
join v_DistributionPointInfo dist on s.ServerNALPath = dist.NALPath
where s.state !=0
Group by p.pkgid, s.installstatus, p.name, p.SourceSize, s.state
Order by Count desc, s.installstatus, p.name