SCCM SQL Query to get the list of applications from a specific folder Name in the console.

Select 

app.DisplayName,

app.Description,

fol.objectpath,

app.CreatedBy,

app.LastModifiedBy,

app.DateCreated,

app.DateLastModified,

assgn.CollectionName [Deployed Collection]

from fn_ListLatestApplicationCIs(1033) app

left join v_ConfigurationItems conf on app.CI_UniqueID = conf.cI_uniqueID 

full join v_ApplicationAssignment assgn on conf.CI_UniqueID=assgn.AssignedCI_UniqueID

JOIN vFolderMembers fol ON fol.InstanceKey = conf.modelName

JOIN vSMS_Folders sms on fol.ContainerNodeID = sms.ContainerNodeID

where fol.objectpath like '/PROD/Application  Deployment/Test' or fol.objectpath like '/DEV/Test' or fol.objectpath like '/CAS/Application Deployment/Test'

order by fol.objectpath

Note: Replace the folder name where its highlighted.