Convert Text to nText

Changing a column having type text to its corresponding type ntext is a little more complicated than changing some of the other field types, for example varchar to nvarchar.

To change a field from type varchar to nvarchar simply requires the column type to be changed, using some code like below:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}VNTweb_Fruit_Products
ALTER COLUMN image_large nvarchar(100)
GO

However, the same is not true when changing a column of type text to type ntext. In this case the process is a little longer requiring the creation of a temporary column:

1. create a temporary column of type ntext
2. copy the existing column data to the new temporary column
3. delete the existing column
4. create a new column (with the original name) of type ntext
5. copy the contents from the temporary column to the new column
6. delete the temporary column

Example SQL code for transferring a column of type text to ntext is given below:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}VNTweb_Fruit_Products ADD TempDescription ntext
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
UPDATE {objectQualifier}VNTweb_Fruit_Products SET TempDescription = Description
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}VNTweb_Fruit_Products DROP COLUMN Description
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}VNTweb_Fruit_Products ADD Description ntext
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
UPDATE {objectQualifier}VNTweb_Fruit_Products SET Description = TempDescription
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}VNTweb_Fruit_Products DROP COLUMN TempDescription
GO