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