December 15, 2007 at 2:09 am
Comments posted to this topic are about the item Change column type on constrained columns
January 28, 2008 at 10:12 am
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
February 8, 2008 at 3:00 am
Perfect! Just something I needed!! Thanks Luiz!
May 12, 2009 at 1:52 pm
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
May 13, 2009 at 1:26 pm
Thanks David.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy