SQL Script: Entity row count

Run the script below on the MSCRM database to quickly get a count of records, for real and customisable entities (not logical).

declare @query nvarchar(max)
set @query=''
select @query=@query+'select count(*) Records,'''+Label+''' Entity, '+ cast(IsCustomEntity as varchar(1)) +' as IsCustomEntity from '+name+' union all ' from dbo.LocalizedLabelAsIfPublishedView a
inner join dbo.EntityAsIfPublishedLogicalView b on a.ObjectId=b.EntityId
where a.ObjectColumnName='LocalizedName'
and IsLogicalEntity=0 and IsCustomizable=1
set @query='select Entity,Records, case when IsCustomEntity=''0'' then ''No'' else ''Yes'' end as CustomEntity from ('+left(@query,len(@query)-10)+') s where s.Records>0 order by Entity'
exec sp_executesql @query
Advertisements