List Applications (Outsystems Metamodel)

SELECT 
    App.[ID] AS [Application_Id],
    App.[NAME] AS [Application_Name],
    App.[DESCRIPTION] AS [Description],
    App.[KEY] AS [Key],
    App.[IS_ACTIVE] AS [Is_Active],
    COUNT(AppMod.[MODULE_ID]) AS [Total_Modules]
FROM 
    [dbo].[OSSYS_APPLICATION] App
LEFT JOIN 
    [dbo].[OSSYS_APP_DEFINITION_MODULE] AppMod ON App.[ID] = AppMod.[APPLICATION_ID]
WHERE 
    App.[IS_ACTIVE] = 1 -- Filters out soft-deleted/inactive applications
GROUP BY 
    App.[ID], 
    App.[NAME], 
    App.[DESCRIPTION], 
    App.[KEY], 
    App.[IS_ACTIVE]
ORDER BY 
    App.[NAME] ASC;