Jim Wang: Get Entity/Attribute's Display Name from CRM database

[Blog bot]

Источник: http://jianwang.blogspot.com/2009/03...name-from.html
==============


The Display Name for CRM Entity/Attribute is always a special case. In CRM 3.0, the Display Name is saved in the table: OrganizationUIBase, column: FieldXml. To get the Display Name for each attributes isn't an easy job. My approach was transfer the FieldXml column(NVARCHAR) into XML type, then get data from there. Here's the code I'd like to show about how to get the Display Name from CRM 3.0 (I suppose that you only want to see entity Account and Contact):



-- Get the display name from xml field
USE [Contoso_MSCRM]
GO
SELECT CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), O.FieldXml),'' ,'')) AS XmlField
INTO #temp1 FROM OrganizationUIBase O
WHERE NOT EXISTS(SELECT 1 FROM OrganizationUIBase WHERE Version>O.Version AND ObjectTypeCode=O.ObjectTypeCode)
SELECT DISTINCT
t2.x.value('(../../@objecttypecode)[1]','int') AS ObjectTypeCode,
t2.x.value('(../../@name)[1]','nvarchar(100)') AS EntityName,
t2.x.value('@name', 'nvarchar(50)') AS AttributeName,
t2.x.value('(displaynames/displayname/@description)[1]','nvarchar(100)') AS DisplayName
INTO #temp2
FROM #temp1 AS t1 CROSS APPLY t1.XmlField.nodes('/entity/fields/field') AS t2(x)

-- Join the metadata database
USE [Contoso_METABASE]
GO
SELECT
Entity.Name AS EntityName,
Attribute.Name AS AttributeName,
#temp2.DisplayName AS AttributeDisplayName,
FROM Attribute
INNER JOIN Entity ON Attribute.EntityId = Entity.EntityId
INNER JOIN #temp2 ON #temp2.AttributeName = Attribute.Name AND #temp2.ObjectTypeCode = Entity.ObjectTypeCode
WHERE EntityName IN ('Account', 'Contact')
ORDER BY EntityName, AttributeName

DROP TABLE #temp1
DROP TABLE #temp2



In CRM 4.0, because it supports multi languages, so the database has been re-designed: the FieldXml field has been abandoned. Instead, Microsoft uses a new table: LocalizedLabelView to save the Entity/Attribute's Display Name, it's much easy to get the Display Name, same example here (English version, the LanguageId is 1033):



USE Contoso_MSCRM
GO

SELECT EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName,
AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label AS AttributeDisplayName
FROM LocalizedLabelView AS LocalizedLabelView_2 INNER JOIN
AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId RIGHT OUTER JOIN
EntityView INNER JOIN
LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON
AttributeView.EntityId = EntityView.EntityId
WHERE LocalizedLabelView_1.ObjectColumnName = 'LocalizedName'
AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName'
AND LocalizedLabelView_1.LanguageId = '1033'
AND LocalizedLabelView_2.LanguageId = '1033'
AND EntityView.Name IN ('Account','Contact')
ORDER BY EntityName, AttributeName




Источник: http://jianwang.blogspot.com/2009/03...name-from.html