Home > MS CRM 4.0 > finding CRM attributes with no data

finding CRM attributes with no data


Over time I’ve been asked to review various existing CRM implementations. One thing I often like to check is whether there are any attributes that are not in use. To this end I created the stored procedure at the end of this post.

The stored procedure lists the attributes in an entity along with how many records contain data in that attribute. The parameters in the procedure allow you to limit the attributes to custom attributes, and to specify a maximum count of values (above which the attribute data won’t be output).

Notes:

  1. The procedure as written would be created in the MSCRM organisation database. You could create it in a different database, and add the databasename.schemaname. prefixes if you want
  2. The procedure uses the supported route of querying the filtered view for an entity. If you have SQL dbo permission, and don’t mind deleted records being included in the counts, then you can run the query against the base view, which is considerably faster. To do this, remove the string ‘Filtered’ from the procedure definition

Use the following SQL to create the stored procedure:

create

proc pExcCheckColumnValues @entityName nvarchar(64), @customOnly bit = 0, @maxThreshold int = nullas

declare

@attrName nvarchar(50), @sql nvarchar(1024)declare

cur cursor fast_forward forselect

a.name from Notable_Solutions_Inc_MSCRM.dbo.attribute ajoin

Notable_Solutions_Inc_MSCRM.dbo.entity e on a.entityid = e.entityidwhere

e.name = @entityName and a.IsLogical = 0 and

((@customOnly = 1 and a.IsCustomField = 1) or isnull(@customOnly, 0) = 0)create

table #tmp (EntityName nvarchar(64), AttributeName nvarchar(50), ValueCount int)open

curfetch

next from cur into @attrNamewhile

@@fetch_status = 0begin

set

@sql = ‘insert #tmp select ”’ + @entityName + ”’, ”’ + @attrName + ”’, count(*) from Notable_Solutions_Inc_MSCRM.dbo.’ + @entityName + ‘ where ‘ + @attrName + ‘ is not null’ +

case when @maxThreshold is not null then ‘ having count(*) <= ‘ + cast(@maxThreshold as nvarchar) else end exec

(@sql)fetch

next from cur into @attrNameend

close

curdeallocate

curselect

* from #tmpgo

To give an example of how to use the procedure, the following will return all custom attributes of the account, where no more than 10 account records have a value in this field:

exec pExcCheckColumnValues @entityName = ‘account’, @customOnly = 1, @maxThreshold = 10

Advertisements
Categories: MS CRM 4.0
  1. sumit
    April 27, 2011 at 12:17 am

    awesome post. where are you des days-Sumit

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: