I don’t know about you but our CRM system seems to grow redundant fields at an alarming rate. Whether its because you have needed to merge other CRM systems in with your own or you fell for the whole “we simply MUST have this random field we won’t care about in a week” yet again, it can sometimes feel like you have entities with more unused fields than used.
One of the biggest difficulties with this is that it can be hard to get an idea of what fields are actually used and which are effectively pointless.
In this post I’m going to show you a fairly naive approach to generating a list of fields that probably aren’t being used. As long as you understand the caveats mentioned below you might find this useful.
There are better ways to do this!
There are ways to have CRM actually tell you which fields are unused via web service calls. In fact you may well have noticed that CRM has a whole dependency tracking mechanism that is pretty sophisticated (though still not infallible).
If you want a more bomb proof approach to determining whether a field is still used then you probably want to tap into the built in Dependency Tracking mechanism via web service calls. You can find out more about that approach here: Dependency Tracking.
There are two reasons I didn’t take that approach even thought it would have been nominally better on paper.
1. I didn’t want to have to start playing with C# code – I wanted an SQL solution that would be ready to go in about 10 mins
2. I only really wanted to know if each field was referenced on a form. I only wanted a rough and ready list of suspect fields that would then warrant further investigation.
In most environments if a custom field doesn’t appear on a form, I’d say there is a very high probability of it being redundant.
The SQL Solution
First a couple of caveats on how this works. It’s not pretty and its not fast:
1. This approach basically identifies all fields for a target entity and then determines if a given field name appears in the SystemForm FormXML column. SystemForm represents all CRM forms and dashboards which is an OK place to start if you want an idea of which fields are not being used.
Remember though – just because a field is not on a form or dashboard this does not mean that it isn’t being referenced by views, workflows, plugin code and so on. That said, when you come to actually delete the field via CRM, CRM’s dependency mechanism will try and guide you to some of those other places that the field is still being used.
2. The performance of this query will blow. I mean seriously blow. If I wasn’t in such a hurry I’d actually be embarrassed for myself right now. It’s basically using a cursor to loop through each field and then for each field doing a separate tablescan-tastic LIKE query against a ha-uge chunk of XML. It’s horrendous. I did also try a LEFT JOIN based variant but it sucked big time as well. That said its not the sort of thing that we need to performance tune and if you use it against a DEV/UAT system you should be fine as long as you’re patient.
As long as you are happy to take the results with a pinch of salt and don’t mind waiting for the results, feel free to give the following a blast.
Bear in mind this was written in about 5 minutes at 11pm so please don’t send me hate mail. After all, I’m a DBA that just used a non-sargable query, to fill a cursor, to table scan a tiny number of results into a table variable. Do you really think you could add to my crippling self loathing right now? 😉
-- INSTRUCTIONS -- Set the targetEntityName parameter, then stand back -- DON'T run on a production system. This query will get -- real table-scanny, real fast DECLARE @targetEntityName VARCHAR(50) = 'account' DECLARE @currentField VARCHAR(250) -- This table variable will store the final list of fields that a naieve search indicates are unused DECLARE @tblUnusedFields TABLE( name VARCHAR(250) ) -- Declare a cursor that extracts the name of ALL known fields for the target entity DECLARE allFieldsCursor CURSOR FOR SELECT DISTINCT MetadataSchema.Attribute.Name FROM MetadataSchema.Attribute LEFT JOIN MetadataSchema.Entity ON MetadataSchema.Attribute.EntityId = MetadataSchema.Entity.EntityId WHERE MetadataSchema.Entity.Name = @targetEntityName AND -- NOTE: Assuming only custom fields are of interest MetadataSchema.Attribute.IsCustomField = 1 OPEN allFieldsCursor -- Loop through all known fields on the target entity and see if we can find any mention of them in the FormXML of SystemFormBase -- NOTE: This is going to run like a *turd* - I'd strongly suggest you only do this on you UAT/DEV version -- DBAs of a sensitive disposition should look away now... FETCH NEXT FROM allFieldsCursor INTO @currentField WHILE @@FETCH_STATUS = 0 BEGIN PRINT('Currently processing ' + @currentField) IF EXISTS (SELECT 1 FROM SystemFormBase WHERE FormXml LIKE '%%' + @currentField + '%') BEGIN PRINT(' ' + @currentField + ' was found on at least one SystemForm FormXML entry') END ELSE BEGIN PRINT(' ' + @currentField + ' was NOT found as part of any SystemForm FormXML') INSERT INTO @tblUnusedFields (name) VALUES (@currentField) END FETCH NEXT FROM allFieldsCursor INTO @currentField END CLOSE allFieldsCursor DEALLOCATE allFieldsCursor -- You didn't just see that... SELECT * FROM @tblUnusedFields