Change column type of column in sql

Question

I am trying to rename a column data type from text to ntext, but getting an error

Msg 4927, Level 16, State 1, Line 1 Cannot alter column 'ColumnName' to be data type ntext. 

The query I use is as follows: -

 alter table tablename alter column columnname ntext null 

Answer 1

Unable to convert. Add a new column as ntext, then copy the converted data to a new column, and then delete the old column. Perhaps this is a large amount of disk space if it is a large table! You should use NVARCHAR (MAX) instead of NTEXT, which will not be supported in the future.

Msg 4927

Answer 2

I expect that you will need to copy the data, i.e. add a column of scratches, fill it; discard the old column; add a new column, copy the data back, delete the zero column:

 ALTER TABLE TableName ADD tmp text NULL GO UPDATE TableName SET tmp = ColumnName GO ALTER TABLE TableName DROP COLUMN ColumnName GO ALTER TABLE TableName ADD ColumnName ntext NULL GO UPDATE TableName SET ColumnName = tmp GO ALTER TABLE TableName DROP COLUMN tmp 

---

For a database-wide application, you can script to extract it from an info schema (note that you must filter out any system tables, etc.):

 SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME+ '] ADD [__tmp] text NULL GO UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME+ '] SET [__tmp] = [' + COLUMN_NAME + '] GO ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME+ '] DROP COLUMN [' + COLUMN_NAME + '] GO ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME+ '] ADD [' + COLUMN_NAME + '] ntext ' + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END + ' GO UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME+ '] SET [' + COLUMN_NAME + '] = [__tmp] GO ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME+ '] DROP COLUMN [__tmp]' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'text' 

Answer 3

In MySQL query:

ALTER TABLE [tableName] CHANGE [oldColumnName] [new column name] [newColumnType];