Query to find content with "enable for on-demand distribution" option set

If you need to determine which of your ConfigMgr packages, applications or update deployment packages has the "enable for on-demand distribution" option enabled, this query will help.  Run it in SQL studio against the ConfigMgr database.  You can adjust the where statement to also look for other configuration settings, like where the persist in cache option is set.  Enjoy.

 

SELECT  pkg.*,

   (PkgFlags&0x01000000)/0x01000000 AS PKG_DO_NOT_DOWNLOAD,
   (PkgFlags&0x02000000)/0x02000000 AS PKG_PERSIST_IN_CACHE,
   (PkgFlags&0x04000000)/0x04000000 AS PKG_USE_BINARY_DELTA_REP,
   (PkgFlags&0x10000000)/0x10000000 AS PKG_NO_PACKAGE,
   (PkgFlags&0x20000000)/0x20000000 AS PKG_USE_SPECIAL_MIF,
   (PkgFlags&0x40000000)/0x40000000 AS PKG_DISTRIBUTE_ON_DEMAND
 FROM   dbo.v_Package pkg  

 where ((PkgFlags&0x40000000)/0x40000000)  = 1