Search for applications that use an external entity of the extension

Search for applications that use an external entity of the extension

  

How can I get applications that use an external entity from the extension, including those that are using create or Update in the entity?

I have the following script:

select  distinct es.NAME NameApplication, en.NAME NameEntity, ex.Name NameExtension
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
whereex.IS_ACTIVE = 1 and
es.IS_ACTIVE = 1 and
en.is_Active = 1 and
en.ESPACE_ID is null and
en.NAME like '%MyTable%'
order by 1,2

The problem is that it also returned me the applications that reference the extension and that is not stuck to the entity of the extension that I need. I need to know only the applications that reference a given entity and whether this entity uses the entity's CREATE or CREATEORUPDATE operations


Hi DBA,

Normally you can use "Find usage in all eSpaces" option to get the list of referenced eSpaces of the particular Entity which has exposed as public, but for the actions, you cannot find reference by all espaces, only current espace you can do it.


Hope this helps,

Balu

Balu wrote:

Hi DBA,

Normally you can use "Find usage in all eSpaces" option to get the list of referenced eSpaces of the particular Entity which has exposed as public, but for the actions, you cannot find reference by all espaces, only current espace you can do it.


Hope this helps,

Balu

Hi Balu

I have more than 20 applications that reference the extension, but just some reference to the entity I'm looking for and these need to know what are doing create or createOrUpdate in the entity. Can not open application by application, I want to make a script in OSMTD (metadata) to find out which applications.


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



Solution