Changing ntext column type to varchar(n) in SQL Server

Question

I'm having a problem when converting all table columns with ntext type in my database. I wrote this query in order to alter my columns, but there is a syntax error:

ALTER TABLE mytable ALTER COLUMN mycolumn 
VARCHAR(SELECT MAX(DATALENGTH(mycolumn)) FROM mytable);

Even though SELECT MAX(DATALENGTH(mycolumn)) FROM mytable is returning the correct number, the query cannot be executed.

The syntax error is:

Incorrect syntax near the keyword 'select'.(which is inside the varchar)

How can I solve the problem?

• Martin Smith: ntext to varchar might cause data loss, Do you need nvarchar?
• Jama A.: Hi Martin, Yes I'm trying to convert from ntext to varchar(max_of_length_of_column) all columns in my table. How data loss happens in my case?
• Martin Smith: Because ntext is double byte and varchar is (under most collations) single byte so not all characters can be represented.

Answer 1

You will need to execute this as dynamic sql because the size of the column cannot be a variable.

DECLARE @Length int = SELECT MAX(DATALENGTH(mycolumn)) FROM mytable
DECLARE @MyTable varchar(100) = 'mytable'
DECLARE @MyColumn varchar(100) = 'mycolumn'
DECLARE @SQL varchar(8000) = 'ALTER TABLE ' + @MyTable +' ALTER COLUMN '+ @MyColumn +' VARCHAR(' + CONVERT(varchar, @Length) + ')'
EXEC(@SQL)

The benefit of this is you could loop over sys.objects and sys.columns to find all ntext columns and convert them to varchar.

Answer 2

I fear you cannot do this inside an ALTER COLUMN statement. At least I've never seen this.

You should do this way:

Calculate the max data length in that column (you're already doing this):

SELECT MAX(DATALENGTH(mycolumn)) FROM mytable

Now just replace the value you got in the previous query:

ALTER TABLE mytable ALTER COLUMN mycolumn VARCHAR(MAXVALUEFROMPREVIOUSQUERY);

• Jama A.: Thanks for your response, but I cannot do as you told. Because query should be dynamic, I cannot do it for my database which has lots of columns.

Answer 3

Trying:

SET @maxLength = SELECT MAX(DATALENGTH(mycolumn)) FROM mytable;
ALTER TABLE mytable ALTER COLUMN mycolumn VARCHAR(@maxLength);

• Jama A.: Thank you, @Zenofo, but I had tried such method already. But it didn't work as well.
• Koerr: Checkout @james-curtis's answer,using DECLARE and EXEC to run SQL query