martedì 10 novembre 2015

AX 2012 - Query sql per estrarre il codice sorgente dal model

Ciao! con questa query sql potete estrarre il codice degli dal database model di Ax


 use "Model_db"  
 IF (OBJECT_ID('tempdb..#SOURCES') IS NULL)  
 begin  
         --drop table #SOURCES  
         SELECT  
                  b.Name  
                  ,b.ElementHandle  
                  ,coalesce(e.ElementTypeName, el.ElementTypeName) [Type]  
                  ,coalesce(pr.Name, b.Name) [Internal Name]  
                  ,parent.Name [Parent Name]  
                  ,d.Name [Layer]  
                  ,a.[LayerId]  
                  ,c.DisplayName [Model Display Name]  
                  ,c.Name [Model Name]  
                  ,a.[ModelId]  
                  ,a.[MODIFIEDDATETIME]  
                  ,a.[MODIFIEDBY]  
                  ,a.[CREATEDDATETIME]  
                  ,a.[CREATEDBY]  
                  ,cast(coalesce(sr.SourceText, sr1.SourceText) as nvarchar(max)) [Source]  
                 INTO #SOURCES   
         --select count(*)  
          FROM [ModelElementData] a   
                                 inner join [ModelElement] b  
                                        on     a.ElementHandle = b.ElementHandle  
                                 inner join [ModelManifest] c  
                                        on a.ModelId = c.ModelId  
                                 inner join [Layer] d  
                                        on a.LayerId = d.Id  
                                 left join [ModelElement] parent  
                                        on b.ParentHandle = parent.ElementHandle  
                                 left join [ModelElement] pr  
                                        on b.ElementHandle = pr.ParentHandle  
                                 left join [ElementTypes] e  
                                        on pr.ElementType = e.ElementType  
                                 left join [Sources] sr  
                                        on sr.SourceHandle = pr.ElementHandle   
                                 left join [ElementTypes] el  
                                        on b.ElementType = el.ElementType  
                                 left join [Sources] sr1  
                                        on sr1.SourceHandle = b.ElementHandle   
          where not b.Name is null  
                 --and  b.Name like '%InterfaceMacroservice%'   
                 --and coalesce(e.ElementTypeName, el.ElementTypeName) like '%Method%'  
                 --and cast(coalesce(sr.SourceText, sr1.SourceText) as nvarchar(max)) like '%InterfaceMacroService%'  
           --order by 1  
         SELECT TBL.name AS ObjName   
                  ,STAT.row_count AS StatRowCount   
                  ,STAT.used_page_count * 8 AS UsedSizeKB   
                  ,STAT.reserved_page_count * 8 AS RevervedSizeKB   
         FROM tempdb.sys.partitions AS PART   
                 INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT   
                         ON PART.partition_id = STAT.partition_id   
                                 AND PART.partition_number = STAT.partition_number   
                 INNER JOIN tempdb.sys.tables AS TBL   
                         ON STAT.object_id = TBL.object_id   
                         --where TBL.name = '#SOURCES'  
         ORDER BY TBL.name;  
         CREATE INDEX IDX_NAME ON #SOURCES(Name)  
 end  
 --------------------------------------------------------------------  
 select top 10 *  
 from #SOURCES  
 where Name ='SalesFormLetter'  

Nella clausola Where dell'ultima riga potete fare i filtri sul nome dell'oggetto.

L'output è fatto così:


Nessun commento:

Posta un commento