Actually the original script won't work if the index is UNIQUE or if it is a NON-CLUSTERED Primary Key. I fixed these two issues in the script Below.
Luiz Barros.
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
*
*
************************************************************************/
ALTER 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),
@ClusteredVARCHAR(20),
@UniqueVARCHAR(20),
@IndexName1 VARCHAR(100)
-- check if table and column really exist
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 -- this will select indexes that use the column which we want to alter
SELECT I.NAME,
C.Name,
P.XTYPE,
CASE WHEN I.IndID=1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END,
CASE WHEN I.Status=2 THEN 'UNIQUE' ELSE '' END
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,@Clustered,@Unique
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 BEGIN
SET @DROPINDEX = @DROPINDEX + ' DROP INDEX '+@TABLE+'.'+@IndexName
SET @createindex = @createindex + ' CREATE '+@Unique+' 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,@Clustered,@Unique
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