SOLVED: SQL to find out which applications use an extension entity

SOLVED: SQL to find out which applications use an extension entity

  

I need to find out which of my applications are referencing an extension entity. I did a SQL, but it brings the applications that use the extension, even if the entity is not referenced. I only need the applications that reference the entity.

Here is an example I made:

SELECT Distinct es.NAME Aplicacao, en.NAME Entidade, ex.Name Extensao
   FROM  OSMTD.dbo.ossys_Espace_Entity ee
                INNER JOIN OSMTD.dbo.ossys_Espace_Extension eex on ee.ESPACE_ID = eex.ESPACE_ID
                INNER JOIN OSMTD.dbo.ossys_Espace es on ee.ESPACE_ID = es.ID and eex.ESPACE_ID = es.ID
                INNER JOIN OSMTD.dbo.ossys_Entity en on ee.ENTITY_ID = en.ID
                INNER JOIN OSMTD.dbo.ossys_Extension ex on eex.Extension_Id = ex.Id and en.Extension_Id = ex.Id
WHERE  ex.IS_ACTIVE = 1 AND
                es.IS_ACTIVE = 1 AND
                en.is_Active = 1 AND
                en.ESPACE_ID is null AND
                en.NAME like '%ContatoPessoa%'

Solution

-- I got the solution:

IF OBJECT_ID('tempdb..#Espace_Version') is not null DROP TABLE #Espace_Version

SELECT MAX(ID) ID, Espace_Id  

     INTO #Espace_Version

   FROM [OSMTD].DBO.OSSYS_ESPACE_VERSION

 GROUP BY Espace_Id


SELECT DISTINCT e.[NAME] ENTITY, r.PRODUCER_NAME EXTENSION

   FROM #Espace_Version v

              Inner JOIN [OSMTD].DBO.[OSSYS_ESPACE] e ON v.[ESPACE_ID] = e.[ID]

              Inner JOIN [OSMTD].DBO.[OSSYS_ESPACE_REFERENCE] r ON r.[CONSUMER_VERSION_ID] = v.[ID]

WHERE

             e.IS_ACTIVE = 1 AND    

             r.[NAME] = 'Name_Entity'

order by 2,1


Thank

Solution