Technical Article

Find Duplicated rows

,

This is a very simple and small stored procedure which will help you identify repeated rows on any given table.  This is specially useful for situations when you want to clean up a table and add a primary key on it, but you cannot until all duplication is eliminated.  Only two parameter are necessary; table name and column name.

CREATE procedure sp_Find_Duplication 
(@Tb_Name varchar(50),@Col_Name varchar(50))
AS

/*************************************************************************************************************
**Object Name: sp_Find_Duplication
**Author:Francisco Macedo
**Calls: 
**
**Variables: @Tb_Name = Name of source table where duplication may be found  name (ie. 'SoShipline')
**   @Col_Name =  Name of column where duplication may exist (ie. 'ShipperID')
**  
**Purpose:  Find repeated rows on any give table.  This is useful to find duplication caused by the lack 
**          of a PK, or corrupted PK.
**Date:  07-08-2002
**************************************************************************************************************/

DECLARE @SQL varchar(1000)


PRINT 'THIS IS THE SELECT STATEMENT:'
PRINT ''
SET @SQL = 'SELECT ' + @Col_name + ',' + ' COUNT(' + @Col_Name + ') AS Repeated_Rows  
FROM ' + @tb_Name +
' GROUP BY ' + @Col_Name + 
' HAVING COUNT(' + @Col_Name + ') >1'

PRINT @SQL
PRINT ''

EXEC (@SQL)

Rate

Share

Share

Rate