Change column type on constrained columns

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Luiz Barros » Change column type on constrained columns

Change column type on constrained columns

Rate Topic

Display Mode

Topic Options

Author

Message

Luiz-458831

Posted Saturday, December 15, 2007 2:09 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 PM
Points: 73, Visits: 445

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

Post #433650

Luiz-458831

Posted Monday, January 28, 2008 10:12 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 PM
Points: 73, Visits: 445

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

Nisha-475382

Posted Friday, February 08, 2008 3:00 AM

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

dhay1999

Posted Tuesday, May 12, 2009 1:52 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 29, 2012 9:12 AM
Points: 21, Visits: 343

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

Luiz-458831

Posted Wednesday, May 13, 2009 1:26 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 PM
Points: 73, Visits: 445

Thanks David.

Post #716361

« Prev Topic | Next Topic »

Permissions