Dynamics CRM

CRM 2011 – Find “Unused” Custom Fields via SQL

Introduction

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.

The dependency tracking mechanism will cover you against all sorts of scenarios that my approach won’t. Off the top of my head it would detect when fields are being referenced via views, workflows and potentially even javascript.

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
Advertisements

4 thoughts on “CRM 2011 – Find “Unused” Custom Fields via SQL

    1. Kinda wishing I’d seen that before I wrote that code!

      That CRM Data Detective tool also looks pretty interesting. The visual approach is fantastic. I’d have to think twice about running it on production given the amount of table scans it would perform but even on a recent UAT build that could be a winner

      Cheers matey

      S

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