SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Luiz Barros / Change column type on constrained columns / Latest Posts

RE: Change column type on constrained columns

Luiz-458831 — Wed, 13 May 2009 13:26:06 GMT

Thanks David.

RE: Change column type on constrained columns

dhay1999 — Tue, 12 May 2009 13:52:00 GMT

Luiz,Wonderful script! I made another small mod to include FileGroup, FillFactor, and put some ;, and CR/LF between each step...set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo/************************************************************************* 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)ASBEGINSET 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)RETURNDECLARE C CURSOR FOR -- this will select indexes that use the column which we want to alterSELECT 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,OrigFillFactorFROM sysobjects OINNER JOIN syscolumns C ON C.ID=O.IDINNER JOIN sysindexes I ON I.ID=O.IDINNER JOIN sysindexkeys IK ON IK.ID=O.ID AND IK.IndID=I.IndID AND IK.ColID=C.ColIDLEFT OUTER JOIN sysobjects P ON P.parent_obj=O.ID AND I.Name=P.Name AND P.XTYPE='PK'WHERE O.Name=@TableAND I.NAME NOT LIKE '_WA_Sys_%'AND I.NAME IN ( SELECT I1.NAME FROM sysobjects O1INNER JOIN syscolumns C1 ON C1.ID=O1.IDINNER JOIN sysindexes I1 ON I1.ID=O1.IDINNER JOIN sysindexkeys IK1 ON IK1.ID=O1.ID AND IK1.IndID=I1.IndID AND IK1.ColID=C1.ColIDWHERE O1.NAME=@TABLE AND C1.NAME=@FIELD)ORDER BY IK.IndID,IK.keynoOPEN CFETCH NEXT FROM C INTO @IndexName,@Name,@XTYPE,@Clustered,@Unique, @groupid, @origFillFactorSET @DROPINDEX= ''SET @CREATEINDEX= ''WHILE @@FETCH_STATUS = 0 BEGINIF @XTYPE='PK' BEGINSET @DROPINDEX = @DROPINDEX + 'ALTER TABLE '+@TABLE+' DROP CONSTRAINT '+@IndexName + ';'SET @CREATEINDEX = @CREATEINDEX + 'Alter Table '+@TABLE +' Add Constraint '+@INDEXNAME+' PRIMARY KEY '+@Clustered+' ('END ELSE BEGINSET @DROPINDEX = @DROPINDEX + 'DROP INDEX '+@TABLE+'.'+@IndexName + ';'SET @CREATEINDEX = @CREATEINDEX + 'CREATE '+@Unique+' INDEX '+@INDEXNAME+' ON '+ @TABLE +'('ENDSET @IndexName1 = @IndexNameSET @PKFIELDS = ''WHILE @@FETCH_STATUS = 0 AND @IndexName1 = @IndexName BEGIN SET @PKFIELDS = @PKFIELDS + @Name + ','FETCH NEXT FROM C INTO @IndexName,@Name,@XTYPE,@Clustered,@Unique, @groupid, @origFillFactorENDSET @CREATEINDEX = @CREATEINDEX + LEFT(@PKFIELDS, LEN(@PKFIELDS)-1) + ')' + ' with fillfactor = ' + ltrim(str(@origFillFactor)) + ' on [' + filegroup_name(@groupid) + '];'--print @dropindex--print @createindexENDCLOSE C DEALLOCATE CIF 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'ELSESET @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 ENDDavid Hay

RE: Change column type on constrained columns

Nisha-475382 — Fri, 08 Feb 2008 03:00:32 GMT

Perfect! Just something I needed!! Thanks Luiz!

RE: Change column type on constrained columns

Luiz-458831 — Mon, 28 Jan 2008 10:12:32 GMT

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 ONset QUOTED_IDENTIFIER ONgo/************************************************************************* 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) ASBEGIN 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) -- 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

Change column type on constrained columns

Luiz-458831 — Sat, 15 Dec 2007 02:09:36 GMT

Comments posted to this topic are about the item [B]Change column type on constrained columns[/B]