I am trying to rename the column datatype from text to ntext but getting error
Msg 4927, Level 16, State 1, Line 1
Cannot alter column 'ColumnName' to be data type ntext.
query that i m using is as follows:-
alter table tablename alter column columnname ntext null
Conversion not allowed. Add new column as ntext then copy converted data to new column, then delete old column. Might consume a lot of diskspace if it's a large table! You should use NVARCHAR(MAX) instead of NTEXT which will not be supported in the future.
I expect you'll need to copy the data out - i.e. add a scratch column, fill it; drop the old column; add the new column, copy the data back, remove the scratch 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 applying database-wide, you can script it out from info-schema (note you should 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'
In MySQL, the query is:
ALTER TABLE [tableName] CHANGE [oldColumnName] [newColumnName] [newColumnType];
