I’m quite often getting a request to check the data quality on CRM environments. You can use SQL Server Management Studio to write select statements to your Dataverse database. Here’s an example of how to find duplicate values.
This is my go-to -query to provide clients a list just to give an understanding of what kind of cleanup operation is going to be needed:
SELECTa.name,a.telephone1,a.accountnumber,a.accountid,a.address1_line1,a.address1_city,a.owneridyominame,‘https://environmenturlhere.crm4.dynamics.com/main.aspx?appid=8402fd82-3&pagetype=entityrecord&etn=account&id=’ + convert(varchar(36), a.accountid) as record_url –update correct account form url here (copy paste from browser and delete the accountid from the endFROM (SELECT *, COUNT(*) OVER (PARTITION BY name,accountnumber) AS cnt –partition by the columns that you want to check the duplicates can be 1 or more.FROM accountwhere statecode = 0 –list only active accounts) aWHERE cnt > 1
You can easily modify the query to check the duplicate values on different columns by just changing the PARTITION BY values.