SQL Script loops modify database field type

The database may not be considered comprehensive at design time, causing some field types to be less accurate. For example, when the design is varchar (1024), but the actual use of the time but found too small, can not fit, so you need to modify the field type of ntext or something.

I recently encountered a need to change the type of 20 fields, write the same script 20 times? No,no,no!

========================================================================

I have such a table "Attribute", there are 60 fields, respectively, ATTRIBUTE01, ATTRIBUTE02, Attribute03 、...... Attribute60. Now I need to change the field type of Attribute41 to Attribute60 to ntext.

SQL Server:

DECLARE @i intSET @i= ADECLARE @fieldName varchar( +)DECLARE @sqlStatement varchar(MAX) while @i<= -BEGIN    SET @fieldName = 'Attribute' + cast(@i  as varchar)        SET @sqlStatement ='alter TABLE [Attribute] Alter COLUMN ['+@fieldName+'] [ntext] NULL'     EXEC(@sqlStatement)        SET @i=@i+1END

Oracle:

DECLAREI Number; FieldNameVARCHAR2( +); FIELDNAMEBKVARCHAR2( +); SQLStatementVARCHAR2(1024x768); BEGINI:= A; LOOP FieldName:='Attribute' ||To_char (i); FIELDNAMEBK:='Attribute' ||To_char (i)|| '_bak'; --working with Clipex tablesSQLStatement:='Alter table Attribute ADD (' ||Fieldnamebk|| 'NCLOB null)'; EXECUTEIMMEDIATE SQLStatement; SQLStatement:='Update Attribute Set' ||Fieldnamebk|| ' = ' ||FieldName; EXECUTEIMMEDIATE SQLStatement; SQLStatement:='ALTER TABLE Attribute drop COLUMN' ||FieldName; EXECUTEIMMEDIATE SQLStatement; SQLStatement:='ALTER TABLE Attribute rename COLUMN' ||Fieldnamebk|| ' to' ||FieldName; EXECUTEIMMEDIATE SQLStatement; I:=I+1; EXIT  whenI>  -; ENDLOOP; COMMIT;END;

In this way, a loop is done.

SQL Script loops modify database field type