SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Change Column datatype

By Devendra Thakur,

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.

Total article views: 430 | Views in the last 30 days: 1
Related Articles

Alter A Partitioned Table Column DataType.

Alter A Datatype of a Table which is Partitioned.


How to alter the datatype

How to alter only datatype for existing column.


how to alter primary key int datatype to uniqueidentifier

how to alter primary key int datatype to uniqueidentifier


Rg: Alter command

Alter multiple column datatypes using sql


Altering column from varchar(2048) to varchar(max) performance

Altering column datatype to varchar(max)

alter table    
generic script