by Al Beecy
March 10, 2009
To simply find duplicate values in a table use the following query (replacing "[ColumnName]" and "[TableName]" with the actual values):
SELECT [ColumnName], COUNT(*) AS [DupeCount]
FROM [TableName]
GROUP BY [ColumnName]
HAVING COUNT(*) > 1
To find duplicate values and list the rows that contain them, use the following query:
SELECT *
FROM [TableName]
WHERE [ColumnName] IN
(
SELECT [ColumnName]
FROM [TableName]
GROUP BY [ColumnName]
HAVING COUNT(*) > 1
)
ORDER BY [ColumnName]
Applies to SQL Server 2005, SQL Server 2008.
Subscribe