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:
- 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
- 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 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
1 comments:
Post a Comment