Microsoft CRM Implementations
(801) 428-1200
You must be logged in and have permission to create or edit a blog.
By Sam Walker on 2/9/2012 10:58 AM

I had the task of seeing which entities in an on-premise CRM 2011 system were being used.  Here’s a SQL script to run against your CRM database to get the entity, the table name, and the count of records.

This can also be used to troubleshoot disk space issues.

DECLARE @Table TABLE (Table_Name VARCHAR(100), Record_Count INT) DECLARE @Table_Name VARCHAR(100) DECLARE CUR CURSOR FOR SELECT name FROM SYSOBJECTS WHERE xtype='U' and name like '%base' OPEN CUR FETCH NEXT FROM CUR INTO @Table_Name WHILE @@FETCH_STATUS=0 BEGIN INSERT INTO @Table EXEC('SELECT '''+@Table_Name+''',(SELECT COUNT(*) FROM ['+@Table_Name+'])') FETCH NEXT FROM CUR INTO @Table_Name END CLOSE CUR DEALLOCATE CUR SELECT ev.DisplayName, T.* FROM @Table T join (SELECT DISTINCT EntityView.Name, EntityView.LogicalName, LocalizedLabelView_1.Label AS DisplayName FROM LocalizedLabelView AS LocalizedLabelView_2 INNER JOIN AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId RIGHT OUTER JOIN EntityView INNER JOIN LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON AttributeView.EntityId = EntityView.EntityId WHERE (LocalizedLabelView_1.ObjectColumnName = 'LocalizedName') AND (LocalizedLabelView_2.ObjectColumnName = 'DisplayName') AND (LocalizedLabelView_1.LanguageId = '1033') AND (LocalizedLabelView_2.LanguageId = '1033')) as ev on T.Table_Name like cast(ev.LogicalName + '%' as nvarchar(30)) order by Record_Count desc
30 Day Free Trial Button
Newsletter Button