Prev: Converting money type into varchar
Next: how to delete/archive huge data from a table without much issues!
From: SqlBeginner on 17 Jun 2010 20:51
What is the easiest way finding duplicates records across all tables in a
given database? I know this looks like a strange question. We found some
duplicate records in few of the important tables within our DB. Now we just
want to make sure duplicates doesn't exist in any of the tables in that
database. Any pointers on that would be good help.

Regards
Pradeep
From: SqlBeginner on 18 Jun 2010 05:57
I thought of the undocumented proc sp_MSFOREACHTABLE but not sure how to use
it in this specific case.

Regards
Pradeep

"SqlBeginner" wrote:

> What is the easiest way finding duplicates records across all tables in a
> given database? I know this looks like a strange question. We found some
> duplicate records in few of the important tables within our DB. Now we just
> want to make sure duplicates doesn't exist in any of the tables in that
> database. Any pointers on that would be good help.
>
> Regards
> Pradeep
From: SetonSoftware on 18 Jun 2010 09:39
On Jun 17, 8:51 pm, SqlBeginner
<SqlBegin...(a)discussions.microsoft.com> wrote:
> What is the easiest way finding duplicates records across all tables in a
> given database? I know this looks like a strange question. We found some
> duplicate records in few of the important tables within our DB. Now we just
> want to make sure duplicates doesn't exist in any of the tables in that
> database. Any pointers on that would be good help.
>
> Regards
> Pradeep

Pradeep

You'd need to look at each table individually and determine which
columns taken together would constitute a unique row. I'm assuming
there is a primary key(s) on each table which would be unique. try
this:

SELECT Column1, Column2, COUNT(*)
FROM MyTable
GROUP BY Column1, Column2
HAVING COUNT(*) > 1

You could build such SQL statements dynamically from the
INFORMATION_SCHEMA.columns view.

Thanks

Carl
From: Erland Sommarskog on 18 Jun 2010 17:42
SqlBeginner (SqlBeginner(a)discussions.microsoft.com) writes:
> What is the easiest way finding duplicates records across all tables in
> a given database? I know this looks like a strange question. We found
> some duplicate records in few of the important tables within our DB. Now
> we just want to make sure duplicates doesn't exist in any of the tables
> in that database. Any pointers on that would be good help.

I agree with SetonSoftware that you need to look at each table individually.
After all, if you have done a proper database design, and defined your
keys, there should be no duplicates at all.

If you have not done this properly, you need per table find what should
be unique. After all, if you have an Address table, and there 17 rows
where City = "Tokyo" does not mean you have a duplicate.
--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Converting money type into varchar
Next: how to delete/archive huge data from a table without much issues!