Finding Unused CRM Fields
Often during an implementation of CRM a lot of attributes get added to entities, or get imported from another environment, and never end up being used for anything. Sometimes those fields are earmarked for ‘future use’, but often times they are just rubbish and are only taking up space (in the abstract sense).
In addition to de-cluttering things (I’m a huge fan), there are performance gains to be had by removing unused fields – especially Lookup and Picklist/OptionSet-type fields. These kinds of fields force CRM to get information from other database tables every time they are referenced, so it’s wasted effort to try to fetch that data when you know there never will be anything to fetch.
Below is a T-SQL script that you or your DBA can run against any given CRM database table to identify fields that contain no data.
Usage Notes:
- Run this script as often as required
- It runs for one table at a time: you need to specify the table name inside the single quotes where, in the example, it says TABLE_NAME=’accountextensionbase’. In this example, the script will examine the custom fields on the Account entity.
- You need only ever run it for ‘extensionbase’ tables in CRM – these are where the custom fields are stored and those are the only ones you can get rid of.
- Copy and paste the code below (everything between the horizontal lines) into a SQL Management Studio query editor window, change your table name, and execute it.
declare @tempTable table
(
TableSchema nvarchar(256),
TableName nvarchar(256),
ColumnName sysname,
ColumnType nvarchar(256),
NotNullCnt bigint
);
declare @sql nvarchar(4000);
declare @tableSchema nvarchar(256);
declare @tableName nvarchar(256);
declare @ColumnType nvarchar(256);
declare @columnName sysname;
declare @cnt bigint;
declare columnCursor cursor for
select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE = ‘YES’ and TABLE_NAME=’accountextensionbase’;
open columnCursor;
fetch next from columnCursor into @tableSchema, @tableName, @columnName, @ColumnType;
while @@FETCH_STATUS = 0
begin
— use dynamic sql to get count of records where column is not null
set @sql = ‘select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
'] where [' + @columnName + '] is not null’;
— print @sql; –uncomment for debugging
exec sp_executesql @sql, N’@cnt bigint output’, @cnt = @cnt output;
insert into @tempTable select @tableSchema, @tableName, @columnName, @ColumnType, @cnt;
fetch next from columnCursor into @tableSchema, @tableName, @columnName, @ColumnType;
end;
close columnCursor;
deallocate columnCursor;
select * from @tempTable where NotNullCnt = 0;
The result set will be something like this:
The 3rd and 4th columns are the ones you are interested in – it tells you the CRM Schema name of the field and what data-type it is. Armed with this information, you can approach the relevant business group and make your case for removing any unwanted fields.
As always, before you begin wildly deleting anything in CRM, make sure you have a backup of your database. Before you are able to delete fields, you will have to ensure that any CRM dependencies (is the field on a form, or in a view, or used in a workflow?) are cleared up.
