-- expand recursive folder structure
WITH FolderHierarchy (ContainerNodeID, RootContainerNodeID) AS (
    	SELECT
			P.ContainerNodeID,
			P.ParentContainerNodeID
    	FROM
			vSMS_Folders P
		WHERE
			P.ParentContainerNodeID = 0
    UNION ALL
    	SELECT
			C.ContainerNodeID,
			C.ParentContainerNodeID
    	FROM
			vSMS_Folders C
		INNER JOIN
			FolderHierarchy R
		ON
			C.ParentContainerNodeID = R.ContainerNodeID
)

SELECT 
	P.PkgID,
	P.Name	
FROM
	FolderHierarchy FH
	JOIN
		vFolderMembers FM
	ON
		FM.ContainerNodeID = FH.ContainerNodeID
	JOIN
		vSMS_ContentPackage P
	ON
		P.SecurityKey = FM.InstanceKey
WHERE
	FH.RootContainerNodeID = (SELECT ContainerNodeID FROM vSMS_Folders WHERE Name ='Production' and ObjectType = 6000)

This script will retreive all applications under a root folder and subfolder hierarchy.