----------------------------------------------------------------
-- SP_IndexPkeyRemove: Remove all index/primary keys for a table
----------------------------------------------------------------

CREATE PROCEDURE IndexPkeyRemove
  @tableName VARCHAR(40)
AS
  BEGIN

  IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
    DROP TABLE #indexes
  CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

  --EXEC sp_helpindex @tableName

  INSERT INTO #indexes
  EXEC sp_helpindex @tableName

  DECLARE indexesCsr CURSOR FOR
  SELECT name, description, keys
  FROM #indexes
  FOR READ ONLY

  DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
  DECLARE @description VARCHAR(210)
  DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
  DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
  DECLARE @description_clus VARCHAR(210)
  DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

  --drop existing indexes, non-clustered first, then clustered, if any
  OPEN indexesCsr
  FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
  WHILE @@FETCH_STATUS = 0
    BEGIN
    IF LEFT(@description, 9) = 'clustered'
      BEGIN
      SET @name_clus = @name
      SET @description_clus = @description
      SET @keys_clus = @keys
      END --IF
    ELSE
      BEGIN
      --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
      --PRINT 'ALTER TABLE ' + RTRIM(@tableName) + ' DROP CONSTRAINT '  + @name
      IF (@description like '%primary key%')
        BEGIN
        --PRINT 'Primary Key found'
        EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name)
        END
      ELSE
        BEGIN
        EXEC('DROP INDEX ' + @tableName +'.' + @name)
        END
        -- IF

      END --ELSE
      FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
    END --WHILE
    CLOSE indexesCsr
    DEALLOCATE indexesCsr
    IF @name_clus IS NOT NULL
      BEGIN
      --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus

      IF (@description like '%primary key%')
        BEGIN
        --PRINT 'Primary Key found'
        EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
        END
      ELSE
        BEGIN
        --IF
        --EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @name_clus)
        --PRINT 'Primary Key NOT found'
        --PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
        EXEC('DROP INDEX ' + @tableName + '.' + @name_clus)
      END

    END --IF
  END
GO

EXECUTE IndexPkeyRemove 'IRGROUPS'
GO

ALTER TABLE IRGROUPS
  ADD CONSTRAINT PK_IRGROUPS
  PRIMARY KEY (UNIQUECOLLECTIONID,GROUPID,VCID);
GO

DROP PROCEDURE IndexPkeyRemove
GO