CODEWITHSUNDEEP
sql-server-2005
Vinay Pandey
Vinay Pandey

Reputation: 8923

Alter column datatype in sql

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

Upvotes: 4

Views: 20698

Answers (3)

Dimitri C.
Dimitri C.

Reputation: 22526

In MySQL, the query is:

ALTER TABLE [tableName] CHANGE [oldColumnName] [newColumnName] [newColumnType];

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1064044

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'

Upvotes: 3

Joakim Backman
Joakim Backman

Reputation: 1907

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.

Msg 4927

Upvotes: 4

Related Questions