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