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 –

Get the list of Columns which are using datatypes char, varchar and text

Fetch the details like table name, datatype, length etc.

Generate the Alter statement(Dynamic SQL)

Execute Dynamic SQL

Capture the status (success or failure) with error details

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.