Duplicate records finding in all tables in a database.

 Duplicate records finding in all tables in a database.

--To find all userdefined tables in the database.

Use databasename
GO
select * from sysobjects where xtype='u'

--then copy the tables in excelsheet column and calculate using below formula in --another excel column

="select id from "&A1&" group by id where having count(*)>1 "

--Id is the primary key column name,A1 is the table cell name.
Finally execute the all tables select query collections from the excel sheet to the database.

Select id from employee group by id  having count(*)>1
Select id from employee1 s group by id  having count(*)>1
Select id from employee2 group by id  having count(*)>1
Select id from employee3 group by id  having count(*)>1

--if any records come,it seems to be presence of duplicate records…

Is any other method to find?

Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)