Change column type on constrained columns

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/************************************************************************
* Stored Procedure: [LSP_ChangeColumnType]            
* Creation Date: 20070806                
* Written by: Luiz Barros                    
*                                    
* Purpose: Alter column type, even if column is part of a constraint, such as a primery key
*                                     
*                                     
************************************************************************/
CREATE PROCEDURE [dbo].[LSP_ChangeColumnType] 
    @Table        VARCHAR(50),
    @Field        VARCHAR(50),
    @NewDataType    VARCHAR(50)
    
AS
BEGIN
    
    SET NOCOUNT ON;
    
DECLARE    @INDEXNAME    VARCHAR(100),
    @SQL        VARCHAR(3000),
    @PKFIELDS        VARCHAR(300),
    @Name        VARCHAR(50),
    @REQUIRED        VARCHAR(10),
    @DROPINDEX    VARCHAR(2000),
    @CREATEINDEX    VARCHAR(2000),
    @XTYPE        VARCHAR(30),
    @IndexName1    VARCHAR(100)
 
    

    -- check if table and column really exists 
    IF NOT EXISTS (SELECT * FROM sysobjects O, syscolumns C WHERE C.ID=O.ID AND O.NAME=@TABLE and O.XTYPE='U' and C.Name=@Field)
     RETURN
     
     
    DECLARE    C CURSOR FOR
    
    SELECT    I.NAME,C.Name,P.XTYPE -- this will select indexes that use the column which we want to alter
    FROM    sysobjects O
        INNER JOIN syscolumns C ON C.ID=O.ID
        INNER JOIN sysindexes I ON I.ID=O.ID
        INNER JOIN sysindexkeys IK ON IK.ID=O.ID AND IK.IndID=I.IndID AND IK.ColID=C.ColID
        LEFT OUTER JOIN sysobjects P ON P.parent_obj=O.ID AND I.Name=P.Name AND P.XTYPE='PK'
    WHERE    O.Name=@Table
        AND I.NAME NOT LIKE '_WA_Sys_%'
        AND I.NAME IN (    SELECT    I1.NAME 
                FROM    sysobjects O1
                    INNER JOIN syscolumns C1 ON C1.ID=O1.ID
                    INNER JOIN sysindexes I1 ON I1.ID=O1.ID
                    INNER JOIN sysindexkeys IK1 ON IK1.ID=O1.ID AND IK1.IndID=I1.IndID AND IK1.ColID=C1.ColID
                WHERE    O1.NAME=@TABLE AND C1.NAME=@FIELD)
    ORDER BY    IK.IndID,IK.keyno

    OPEN    C
    FETCH    NEXT FROM C INTO @IndexName,@Name,@XTYPE

    
    SET @DROPINDEX= ''
    SET @CREATEINDEX= ''
    WHILE @@FETCH_STATUS = 0 BEGIN
         
         IF @XTYPE='PK' BEGIN
         SET @DROPINDEX = @DROPINDEX + ' ALTER TABLE '+@TABLE+' DROP CONSTRAINT '+@IndexName
         SET @CREATEINDEX = @CREATEINDEX + ' Alter Table '+@TABLE +' Add Constraint '+@INDEXNAME+' PRIMARY KEY CLUSTERED ('
         END ELSE IF EXISTS(SELECT * FROM SYS_INDICES WHERE TABELA=@TABLE AND @TABLE+'_'+NOME=@INDEXNAME AND UNICO='S') BEGIN
         SET @DROPINDEX = @DROPINDEX + ' DROP INDEX '+@TABLE+'.'+@IndexName
         SET @CREATEINDEX = @CREATEINDEX + ' CREATE UNIQUE INDEX '+@INDEXNAME+' ON '+ @TABLE +'('
         END ELSE BEGIN
         SET @DROPINDEX = @DROPINDEX + ' DROP INDEX '+@TABLE+'.'+@IndexName
         SET @CREATEINDEX = @CREATEINDEX + ' CREATE INDEX '+@INDEXNAME+' ON '+ @TABLE +'('
         END
         
         SET @IndexName1 = @IndexName
         SET @PKFIELDS = ''
         WHILE @@FETCH_STATUS = 0 AND @IndexName1 = @IndexName BEGIN 
         SET @PKFIELDS = @PKFIELDS + @Name + ','
         FETCH NEXT FROM C INTO @IndexName,@Name,@XTYPE
         END
         
         SET @CREATEINDEX = @CREATEINDEX + LEFT(@PKFIELDS, LEN(@PKFIELDS)-1) + ')'
    END

    CLOSE C DEALLOCATE C
 
 IF EXISTS (SELECT * FROM sysobjects O, syscolumns C WHERE C.ID=O.ID AND O.Name=@Table AND C.NAME=@FIELD AND C.isnullable=0) -- is a required field?
 SET @REQUIRED = ' NOT NULL'
 ELSE
 SET @REQUIRED = ''
 
 SET @SQL = @DROPINDEX + ' ALTER TABLE '+@TABLE +' ALTER COLUMN '+@FIELD +' '+@NewDataType+' '+ @REQUIRED + @CREATEINDEX 
         
 
 EXEC(@SQL)
 PRINT @Table+' - '+@Field 
END