if exists (select * from dbo.sysobjects
  where id = object_id(N'[dbo].[uspEvoDeleteDuplicates]')
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[uspEvoDeleteDuplicates]
GO


create proc uspEvoDeleteDuplicates (
  @tableName varchar(100),
  @keepId varchar( 3) = 'max' )
as
  set nocount on

  declare @res varchar( 7000)
  declare @idCol sysname
  declare @groupCols varchar( 7000)
  set @groupCols = ''

  IF object_id('tempdb..#tmpColumns') IS NOT NULL
  BEGIN
     DROP TABLE #tmpColumns
  END

  create table #tmpColumns (
    TABLE_QUALIFIER	sysname,
    TABLE_OWNER	sysname,
    TABLE_NAME	sysname,
    COLUMN_NAME	sysname,
    DATA_TYPE	smallint,
    TYPE_NAME	sysname,
    [PRECISION]	int,
    LENGTH	int,
    SCALE	smallint,
    RADIX	smallint,
    NULLABLE	smallint,
    REMARKS	varchar(254),
    COLUMN_DEF	nvarchar(4000),
    SQL_DATA_TYPE	smallint,
    SQL_DATETIME_SUB	smallint,
    CHAR_OCTET_LENGTH	int,
    ORDINAL_POSITION	int,
    IS_NULLABLE	varchar(254),
    SS_DATA_TYPE	tinyint
  )

  insert into #tmpColumns
  exec sp_columns @tableName

  declare @curColumn sysname
  declare @curTypeName sysname

  DECLARE columnCursor CURSOR local FOR
    SELECT column_name, type_name
    from #tmpColumns
  OPEN columnCursor

  set @res = 'select * from' + CHAR( 13) + '-- delete from' + 
             CHAR(13) + '  ' + @tableName  + CHAR( 13)
  FETCH NEXT FROM columnCursor INTO
    @curColumn, @curTypeName
  WHILE (@@fetch_status <> -1) BEGIN
    if @curTypeName <> 'int identity' begin
      if @groupCols = '' begin
        set @groupCols = @curColumn
      end
      else begin
        set @groupCols = @groupCols + ', ' + @curColumn
      end
    end
    else begin
      set @idCol = @curColumn
    end
    FETCH NEXT FROM columnCursor INTO
      @curColumn, @curTypeName
  END
  DEALLOCATE columnCursor

  set @res = @res + 'where' + CHAR(13) + '  ' + @idCol + ' in (' + 
             CHAR(13) + '  select ' + @keepId + '(' + @idCol + 
             ') from ' + @tableName + CHAR( 13)+ '  group by ' + 
             @groupCols + ')'

  IF object_id('tempdb..#tmpColumns') IS NOT NULL
  BEGIN
     DROP TABLE #tmpColumns
  END

  print @res

  set nocount off

GO