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