CRM Identify which fields are not being used for any given crm entity

We're currently working with a customer who has a CRM 4 deployment that is deemed a failure in the business (this was implemented by another CRM ISV). One of the activities we are undertaking is to simplify the CRM forms as much as possible.

David Jennaway has a small post on identifing unused fields that will be really useful to us to provide evidence which fields are not being used, now if we could just get that data cross referenced with the fields that are actually on the forms or in views....


Use the following SQL to create the stored procedure:
create proc pExcCheckColumnValues @entityName nvarchar(64), @customOnly bit = 0, @maxThreshold int = null
as

declare @attrName nvarchar(50), @sql nvarchar(1024)
declare cur cursor fast_forward for
select a.name from attribute a
join entity e on a.entityid = e.entityid
where 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 cur
fetch next from cur into @attrName
while @@fetch_status = 0
begin
 set @sql = 'insert #tmp select ''' + @entityName + ''', ''' + @attrName + ''', count(*) from Filtered' + @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 @attrName
end


close cur
deallocate cur
select * from #tmp
go

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