MDS 2012 – Change Attribute order with SQL

If you need to change the order of attributes in an MDS entity, there is the correct way to do it, and then there is the easy way to do it ;o)

First the correct way:

Locate your Entity i MDS System Administration, select the Entity and Edit the Entity

Now select the attribute that needs another sorting, and use the arrow up/down to select the correct place.

 

 

The much easier method

This method involves a knowledge of the table structure in MDS, and it is advised not to do this in a production environment!

Open SQL Server Management Studio, select you MDS database.

Find your entity with this SQL :

SELECT ENTITY_ID, NAME FROM MDM.VIW_SYSTEM_SCHEMA_ENTITY
WHERE NAME LIKE '%_MONTHLYPOSTINGS'

Now you have the Entity_ID for the entity you need to change attribute order on.

Run this script:

SELECT 'UPDATE MDM.TBLATTRIBUTE 
SET SORTORDER = ' + CAST(A.SORTORDER AS VARCHAR(3)) + ' 
WHERE ENTITY_ID = ' + CAST(A.ENTITY_ID AS VARCHAR(4)) + ' 
AND NAME = ''' + A.NAME + '''' 
FROM MDM.TBLATTRIBUTE A WHERE A.ENTITY_ID = 156

This will generate a list of updates just like this list:

UPDATE MDM.TBLAttribute SET SortOrder = 1 WHERE Entity_ID = 156 and NAME = 'ID'
UPDATE MDM.TBLAttribute SET SortOrder = 2 WHERE Entity_ID = 156 and NAME = 'Version_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 3 WHERE Entity_ID = 156 and NAME = 'Status_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 4 WHERE Entity_ID = 156 and NAME = 'ValidationStatus_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 5 WHERE Entity_ID = 156 and NAME = 'EnterDTM'
UPDATE MDM.TBLAttribute SET SortOrder = 6 WHERE Entity_ID = 156 and NAME = 'EnterUserID'
UPDATE MDM.TBLAttribute SET SortOrder = 7 WHERE Entity_ID = 156 and NAME = 'EnterVersionID'
UPDATE MDM.TBLAttribute SET SortOrder = 8 WHERE Entity_ID = 156 and NAME = 'LastChgDTM'
UPDATE MDM.TBLAttribute SET SortOrder = 9 WHERE Entity_ID = 156 and NAME = 'LastChgUserID'
UPDATE MDM.TBLAttribute SET SortOrder = 10 WHERE Entity_ID = 156 and NAME = 'LastChgVersionID'
UPDATE MDM.TBLAttribute SET SortOrder = 11 WHERE Entity_ID = 156 and NAME = 'LastChgTS'
UPDATE MDM.TBLAttribute SET SortOrder = 12 WHERE Entity_ID = 156 and NAME = 'Name'
UPDATE MDM.TBLAttribute SET SortOrder = 13 WHERE Entity_ID = 156 and NAME = 'Code'
UPDATE MDM.TBLAttribute SET SortOrder = 15 WHERE Entity_ID = 156 and NAME = 'MUID'
UPDATE MDM.TBLAttribute SET SortOrder = 16 WHERE Entity_ID = 156 and NAME = 'AsOf_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 17 WHERE Entity_ID = 156 and NAME = 'ChangeTrackingMask'
UPDATE MDM.TBLAttribute SET SortOrder = 18 WHERE Entity_ID = 156 and NAME = 'Description'
UPDATE MDM.TBLAttribute SET SortOrder = 30 WHERE Entity_ID = 156 and NAME = 'Comment'
UPDATE MDM.TBLAttribute SET SortOrder = 14 WHERE Entity_ID = 156 and NAME = 'Item'
UPDATE MDM.TBLAttribute SET SortOrder = 31 WHERE Entity_ID = 156 and NAME = 'Approved'

Now just adjust the number in SortOrder, and execute the updates.

Remember to update any subscription views on the Entity

This is way easier than using the System Administration website.

Leave a Reply

2 comments

  1. Robert says:

    Thanks… the update script works great. My situation actually required the “easy” method since many of my sort orders had the same value.