Change column type on constrained columns

  • Comments posted to this topic are about the item Change column type on constrained columns

  • 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

  • Perfect! Just something I needed!! Thanks Luiz!

  • Luiz,

    Wonderful script! I made another small mod to include FileGroup, FillFactor, and put some ;, and CR/LF between each step...

    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

    *

    * Modified by David Hay 20090512 Add OrigFillFactor and FileGroup, CR/LF plus terminators

    *

    ************************************************************************/

    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),

    @Clustered VARCHAR(20),

    @Unique VARCHAR(20),

    @IndexName1 VARCHAR(100),

    @OrigFillFactor int,

    @groupId int

    -- 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,

    Groupid,

    OrigFillFactor

    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, @groupid, @origFillFactor

    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, @groupid, @origFillFactor

    END

    SET @createindex = @createindex + LEFT(@PKFIELDS, LEN(@PKFIELDS)-1) + ')' + ' with fillfactor = ' + ltrim(str(@origFillFactor)) + ' on [' + filegroup_name(@groupid) + '];'

    --print @dropindex

    --print @createindex

    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 = ltrim(@DROPINDEX) + 'ALTER TABLE '+@TABLE +' ALTER COLUMN '+@FIELD +' '+@NewDataType+@REQUIRED + ';' + ltrim(@CREATEINDEX)

    set @sql = replace(@sql,';', ';' + (char(13)))

    print @sql

    --uncomment below to execute not just generate t-sql

    --EXEC(@SQL)

    PRINT @Table+' - '+@Field

    END

    David Hay


    david hay

  • Thanks David.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply