Get a list of searchable attributes from database

(The final solution is at the bottom)

Today my colleague wanted to check which attributes were marked as searchable in a specific entity.

One way to do this is to open each attribute in MSCRM and check if it is marked as searchable. A long way if you have an entity with 220 attributes.

So I went to the database and checked the MetadataSchema.Attribute table. There is a column named DisplayMask. SDK says that display masks are following (ValidForAdvancedFind = Searchable):

Name	Value	Description
None	1	Specifies no restrictions.
ObjectTypeCode	4	Specifies that the attribute is an entity type code. The name of the entity is displayed.
PrimaryName	2	Specifies to display the attribute as the primary name.
RequiredForForm	0x40	Specifies that the attribute must be shown on a form.
RequiredForGrid	0x80	Specifies that the attribute can be shown in a grid view.
ValidForAdvancedFind	8	Specifies that the attribute can be shown on the Advanced Find form. This attribute is shown as Searchable in the UI.
ValidForForm	0x10	Specifies that the attribute can be shown on a form.
ValidForGrid	0x20	Specifies that the attribute can be shown in a grid view.

But the value in the database is 469762048 (this attribute is searchable)! Bitwise comparison fails: 469762048 & 8 = 0 WTF!!!

After some googling and 30 minutes of using reflector if found that the former values are meant to be used only when working with metadata web service, like in this example: http://msdn.microsoft.com/en-us/library/cc151072.aspx

Thank you Reflector!

Microsoft.Crm.Platform.Sdk.dll (found it in GAC) contains DisplayMasks enumeration in Microsoft.Crm.Metadata namespace.

This is what the reflector says:

public enum DisplayMasks
{
  ActivityPointerRegardingName = 0x80,
  ActivityRegardingName = 1,
  BitMask = 0x1000,
  DynamicFormatCode = 0x4000,
  None = 0,
  ObjectTypeCode = 0x400,
  PrimaryName = 0x100,
  QueueItemPriority = 0x10,
  QueueItemSender = 0x20,
  QueueItemState = 4,
  QueueItemStatus = 8,
  QueueItemTitle = 2,
  QueueItemToRecipients = 0x40,
  RequiredForForm = 0x20000000,
  RequiredForGrid = 0x40000000,
  ResourcePointerName = 0x200,
  ReturnedTypeCode = 0x800,
  StateCode = 0x2000,
  ValidForAdvancedFind = 0x4000000,
  ValidForForm = 0x8000000,
  ValidForGrid = 0x10000000
}

This means that the ValidForAdvancedFind has value of 0x4000000! I tested it with the previous example and it works! Hooray!

Solution

So the final answer to the problem of this post is:

select * from MetadataSchema.Attribute
where DisplayMask & 0x4000000 > 0 and EntityId = @entityId

Of course you can use all other values as well.

What I haven’t tested yet is whether you can change this mask value directly in the database - definitely not supported by Microsoft :)