SET NOCOUNT ON
DECLARE @TableWithForeignKey VARCHAR(256)
DECLARE @FK_PartNo INT
DECLARE @ForeignKeyColumn VARCHAR(256)
DECLARE @PrimaryColumnName VARCHAR(256)
DECLARE @sql NVARCHAR(2000)
DECLARE @Pk_ConstraintName VARCHAR(256)

ALTER TABLE WareGroups ADD new_WareGroupNumber INT NULL;
 
EXEC sp_executesql N'UPDATE WareGroups SET new_WareGroupNumber = WareGroupNumber'
 
ALTER TABLE WareGroups ALTER COLUMN new_WareGroupNumber INT NOT NULL;
 
DECLARE #db_cur CURSOR SCROLL STATIC FOR
  SELECT t.name, fk.constraint_column_id, o.name, c.Name
  FROM sys.foreign_key_columns AS fk
  INNER JOIN sys.tables AS t ON fk.parent_object_id = t.object_id
  INNER JOIN sys.columns AS c ON fk.parent_object_id = c.object_id 
    AND fk.parent_column_id = c.column_id
  INNER JOIN sys.objects AS o ON o.object_id = fk.constraint_object_id 
  WHERE fk.referenced_object_id = (select object_id FROM sys.tables WHERE name = 'WareGroups')
OPEN #db_cur

FETCH NEXT FROM #db_cur INTO @TableWithForeignKey, @FK_PartNo, @ForeignKeyColumn, @PrimaryColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'ALTER TABLE ' + @TableWithForeignKey + ' DROP CONSTRAINT ' + @ForeignKeyColumn
  EXEC sp_executesql @sql
  FETCH NEXT FROM #db_cur INTO @TableWithForeignKey, @FK_PartNo, @ForeignKeyColumn, @PrimaryColumnName
END
 
SELECT @Pk_ConstraintName = Name
FROM sysobjects
WHERE xtype = 'PK'
AND parent_obj = OBJECT_ID('WareGroups')
                                                        
SET @SQL = 'ALTER TABLE WareGroups DROP CONSTRAINT ' + @Pk_ConstraintName
EXEC sp_executesql @sql
 
ALTER TABLE WareGroups DROP COLUMN WareGroupNumber
 
EXEC sp_rename 'WareGroups.new_WareGroupNumber', 'WareGroupNumber', 'COLUMN';
 
ALTER TABLE WareGroups ADD CONSTRAINT PKWareGroups PRIMARY KEY CLUSTERED (WareGroupNumber ASC)
 
FETCH FIRST FROM #db_cur INTO @TableWithForeignKey, @FK_PartNo, @ForeignKeyColumn, @PrimaryColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'ALTER TABLE ' + @TableWithForeignKey + ' WITH NOCHECK ADD CONSTRAINT ' + @ForeignKeyColumn + ' FOREIGN KEY (' + @PrimaryColumnName + ') REFERENCES WareGroups (WareGroupNumber)'
  EXEC sp_executesql @sql

  FETCH NEXT FROM #db_cur INTO @TableWithForeignKey, @FK_PartNo, @ForeignKeyColumn, @PrimaryColumnName
END

CLOSE #db_cur
DEALLOCATE #db_cur