Eliminating duplicate rows in SQL Server 2000

Tags:
SQL Server 2000
SQL Server database
I have a database I recently inherited in SQL 2000 that contains some duplicate rows. How can I identify the tables that have this problem (duplicate rows meaning ones that have the same “time_stamp” value, and “time_stamp” being used as a raw identifier)?
ASKED: June 18, 2008  3:56 PM
UPDATED: June 23, 2008  6:59 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You would need to query each table one at a time and scan each table looking for duplicate values.

Code similar to this can be used for each table.

<pre>SELECT time_stamp, count(*)
FROM TableName
GROUP BY time_stamp
HAVING count(*) <> 1</pre>

The rows which are shown are the ones with the duplicate values.

If every table has a column named time_stamp you can use the system procedure sp_MSforeachtable to check each table.

<pre>exec sp_MSforeachtable ‘SELECT time_stamp, count(*)
FROM ?
GROUP BY time_stamp
HAVING count(*) <> 1′</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following