Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Change column type on constrained columns Expand / Collapse
Author
Message
Posted Saturday, December 15, 2007 2:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 9:24 AM
Points: 74, Visits: 534
Comments posted to this topic are about the item Change column type on constrained columns
Post #433650
Posted Monday, January 28, 2008 10:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 9:24 AM
Points: 74, Visits: 534
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),
@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






Post #448446
Posted Friday, February 8, 2008 3:00 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364

Perfect! Just something I needed!! Thanks Luiz!
Post #453125
Posted Tuesday, May 12, 2009 1:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 7:30 AM
Points: 21, Visits: 348

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
Post #715440
Posted Wednesday, May 13, 2009 1:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 9:24 AM
Points: 74, Visits: 534
Thanks David.
Post #716361
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse