Technical Article

Change Column datatype

,

Recently we got the information from management that our project will be getting the customers from China, so our application should support the Unicode characters. Now we support only English language, to support all the languages we decided to change all the char, varchar and text datatypes to nchar, nvarchar and ntext. Process to achieve this is below –
  1. Get the list of Columns which are using datatypes char, varchar and text
  2. Fetch the details like table name, datatype, length etc.
  3. Generate the Alter statement(Dynamic SQL)
  4. Execute  Dynamic SQL
  5. Capture the status (success or failure) with error details
  6. Analyze the error message and manually update the datatypes for failed records
This process has reduced the manual work by 80% (approximately) in our case.

We found that some Alter statements failed due to Default constraints or Column being used in Index. We dropped the constraints, Alter the datatype and recreated them. Since the failed percentage was very less, so we did not tried to automate them.
BEGIN

/*Create the table to collect the column details and status*/IF OBJECT_ID (N'ColumnDataTypeChange', N'U') IS NOT NULL
BEGIN
Truncate Table ColumnDataTypeChange
END
Else
BEGIN
Create Table ColumnDataTypeChange
(
ID int identity(1,1) Primary Key,
TableName nVarchar(1000),
ColumnName nVarchar(1000),
DataType nVarchar(1000),
ColumnSize int,
isUpdated bit,
ErrorMsg  nVarchar(1000),
SQL_Script nVarchar(2000)
)
END
/*Fetch the details of columns*/Insert into ColumnDataTypeChange
SELECT distinct O.name as 'TableName',C.name as 'ColumnName',t.name as 'DataType',C.max_length as 'ColumnSize',
 0 as isUpdated, NULL as ErrorMsg,NULL as SQL_Script
FROM   
    sys.columns c
INNER JOIN
    sys.types t ON c.user_type_id = t.user_type_id
Inner Join sys.objects O on O.object_id=C.object_id and O.type='U'
WHERE T.name='varchar' or T.name='char' or T.name='text'

Declare @TotalRec int
Declare @Count int
Declare @TableName nVarchar(1000)
Declare @ColumnName nVarchar(1000)
Declare @DataType nVarchar(1000)
Declare @ColumnSize int
Declare @SQL nVarchar(1000)
Declare @ErrorMsg nVarchar(1000)

SET @Count=1
SET @TotalRec=(SELECT COUNT(*) FROM ColumnDataTypeChange T)
/*Generate Alter statement and execute them*/While @Count<=@TotalRec
BEGIN

BEGIN TRY
SET @TableName =(SELECT TOP 1 T.TableName FROM ColumnDataTypeChange T WHERE T.ID=@Count )
SET @ColumnName =(SELECT TOP 1 T.ColumnName FROM ColumnDataTypeChange T WHERE T.ID=@Count )
SET @DataType =(SELECT TOP 1 T.DataType FROM ColumnDataTypeChange T WHERE T.ID=@Count )
SET @ColumnSize =(SELECT TOP 1 T.ColumnSize FROM ColumnDataTypeChange T WHERE T.ID=@Count )

SET @SQL='ALTER TABLE dbo.'+@TableName+' ALTER COLUMN '+'['+@ColumnName+']'+' '+'n'+@DataType+'('+CONVERT(nvarchar,@ColumnSize)+')'

EXEC(@SQL)

      Update ColumnDataTypeChange
      SET isUpdated=1
      WHERE ID=@Count

END TRY

BEGIN CATCH 
    /*Capture the error details in case of failure*/ 
      SET @ErrorMsg=(SELECT ERROR_MESSAGE() AS ErrorMessage)

      Update ColumnDataTypeChange
      SET isUpdated=0
      , ErrorMsg= @ErrorMsg
      ,SQL_Script=@SQL
      WHERE ID=@Count
    
END CATCH;


SET @Count=@Count+1
END-- While ENDs

END

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating