Exploring AX 2012 metadata via T-SQL
Well, here's another long overdue blog post. I will minimize the preamble and restrict myself to noting that I've been meaning to publish this for a while. I have now been inspired to get on with it thanks to an excellent presentation at the Microsoft Dynamics Technical Conference 2016, given by Peter Villadsen, on AX 7 metadata.
This is not nearly as slick as some of what he was showing us, but nevertheless, I have found it useful for efficiently exploring the model store in the various releases of AX 2012. I am not going to spend time explaining it in detail. You can read the T-SQL for that, and indeed I encourage you to do so, in order to understand exactly what it is doing, and what it might be used for. Take particular note of the comments, where I have provided some examples illustrating how I tend to use it. Beyond that, let your imagination lead you where it will.
As always, any feedback, enhancements or comments are welcome.
Martin
---Start of T-SQL---
/*
T-SQL script for exploring AX 2012 model store database.
written by Martin Walker (mwalker@syndaxis.com).
Suggestion: For easy reference, copy and paste here the results of [Development workspace->Tools->Model management->Models installed].
*/
use MicrosoftDynamicsAX_model --replace with your model store DB if different
select --top 100
SYSMODELMANIFEST.DISPLAYNAME as ModelDisplayName,
Layer =
case SYSMODELLAYER.LAYER
when '0' then 'sys' --sys
when '1' then 'syp' --syp
when '2' then 'gls' --gls
when '3' then 'glp' --glp
when '4' then 'fpk' --fpk
when '5' then 'fpp' --fpp
when '6' then 'sln' --sln
when '7' then 'slp' --slp
when '8' then 'isv' --isv
when '9' then 'isp' --isp
when '10' then 'var' --var
when '11' then 'vap' --vap
when '12' then 'cus' --cus
when '13' then 'cup' --cup
when '14' then 'usr' --usr
when '15' then 'usp' --usp
end, --UtilEntryLevel
SYSMODELELEMENTTYPE.NAME as ElementTypeName,
SYSMODELELEMENT.NAME as ElementName,
SYSMODELELEMENT.RECID as ElementId,
SYSMODELELEMENT.AXID,
SYSMODELELEMENT.PARENTMODELELEMENT,
SYSMODELELEMENT.PARENTID,
PSME.NAME as ParentName
--,PPSME.NAME as PParentName --If you need the name of the parent element's parent as well.
--,PPPSME.NAME as PPParentName --Etc. See also below.
--,PPPPSME.NAME as PPPParentName --Etc. See also below.
--,PPPPPSME.NAME as PPPPParentName --Etc. See also below.
,COALESCE(PPPPPSME.NAME,PPPPSME.NAME,PPPSME.NAME,PPSME.NAME,PSME.NAME,SYSMODELELEMENT.NAME) as RootName --We are usually most interested in the ultimate parent
--,*
from SYSMODELELEMENT (nolock)
join SYSMODELELEMENTTYPE (nolock)
on SYSMODELELEMENTTYPE.RECID = SYSMODELELEMENT.ELEMENTTYPE
join SYSMODELELEMENTDATA (nolock)
on SYSMODELELEMENTDATA.MODELELEMENT = SYSMODELELEMENT.RECID
join SYSMODELLAYER(nolock)
on SYSMODELLAYER.RECID = SYSMODELELEMENTDATA.LAYER
join SYSMODELMANIFEST (nolock)
on SYSMODELMANIFEST.RECID = SYSMODELELEMENTDATA.MODELID
left outer join SYSMODELELEMENT PSME (nolock) on PSME.RECID=SYSMODELELEMENT.PARENTMODELELEMENT
left outer join SYSMODELELEMENT PPSME (nolock) on PPSME.RECID=PSME.PARENTMODELELEMENT --If you need the name of the parent element's parent as well
left outer join SYSMODELELEMENT PPPSME (nolock) on PPPSME.RECID=PPSME.PARENTMODELELEMENT --Etc.
left outer join SYSMODELELEMENT PPPPSME (nolock) on PPPPSME.RECID=PPPSME.PARENTMODELELEMENT --Etc.
left outer join SYSMODELELEMENT PPPPPSME (nolock) on PPPPPSME.RECID=PPPPSME.PARENTMODELELEMENT --Etc.
--A bit expensive, but if you really want to know what model each element's parent element is in (and add to the fields selected above of course)...
/*left outer join SYSMODELELEMENTDATA PSMED
on PSMED.MODELELEMENT = PSME.RECID
left outer join SYSMODELMANIFEST PSMM
on PSMM.RECID = PSMED.MODELID*/
where SYSMODELELEMENT.RECID in ( --doing this as a subquery to make the element selection criteria more readable
select SME.RECID
from SYSMODELELEMENT SME (nolock)
join SYSMODELELEMENTDATA SMED (nolock)
on SMED.MODELELEMENT = SME.RECID
join SYSMODELMANIFEST SMM (nolock)
on SMM.RECID = SMED.MODELID
--and SMM.DISPLAYNAME like '%cus%' --See elements existing in named model(s). This also implies the corresponding layer(s) since a model only exists in one layer
and SMM.MODEL in ( --I usually prefer to just list the model IDs I'm investigating...
--11, --VAR model --I often add comments here about the model and my plan for it, e.g. during a code merge. I also note the layer if it's not obvious.
--13, --CUS model
15--, --USR model
--22--, --DIXF (cus layer) --Example comment: Delete before R2 cu7 upgrade, only MainMenu and DMFEntityType enum were overloaded, in cus.
--25 --KBxxxxxx --e.g. if we're investigating the impact of a hotfix model, usually for a look at what is overloaded, possibly in multiple higher layers
)
--Additional joins to narrow the results to elements also existing in a different layer from that implied by the model(s) above.
--Comment these joins out to get _all_ elements existing in the model(s) above, whether or not they exist in multiple layers.
/*
join SYSMODELELEMENT SME2 (nolock)
on SME2.RECID = SME.RECID
join SYSMODELELEMENTDATA SMED2 (nolock)
on SMED2.MODELELEMENT = SME2.RECID
join SYSMODELLAYER SML2 (nolock)
on SML2.RECID = SMED2.LAYER
and SML2.LAYER < 14 --in (10,12) --Elements _also_ existing in one or more of the given layers, e.g. "< 14" finds everything overloaded by usr layer model(s) specified earlier.
--and SML2.LAYER > 1 --Another example: Find hotfix model elements which are overloaded. The hotfix would be in syp, hence find the same elements modified above syp.
*/
) -- end of element selection criteria
--Now filter the results further as desired. The following are just some examples, but use your imagination:
--and --uncomment for any of the refining criteria below
--SYSMODELELEMENT.RECID = 431099 --Element ID
--SYSMODELLAYER.LAYER = 14 --> 4 --in (12,13,14,15) --only see information for given layer(s), instead of all the layers for elements selected above
--SYSMODELELEMENT.PARENTMODELELEMENT = 197339
--SYSMODELELEMENT.AXID = 1032311
--(SYSMODELELEMENT.NAME = 'validateURL' /*or PSME.NAME = 'DocuType'*/)
--SYSMODELELEMENTTYPE.NAME like 'Table%'
order by RootName,ElementId,SYSMODELLAYER.LAYER --I usually like to order the results by the root element, so I can just work my way down through the AOT and compare, etc.
---End of T-SQL---
|
|