Requirement: 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.
/*####################################################################*/
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
/*####################################################################*/
Please share your thoughts and feel free to suggest if you
think there is a better way to do this.
