Eliminating duplicate rows in SQL Server 2000
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)?
Software/Hardware used:
Software/Hardware used:
ASKED:
June 18, 2008 3:56 PM
UPDATED:
June 23, 2008 6:59 AM
Answer Wiki:
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>
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.