How can I easily convert all ntext fields to nvarchar(max) in SQL query?

Question

I would love to be able to write a SQL query, without enumerating the columns, which will return me all the columns, and any ntext column converted to varchar(max). I was wondering if there is a clever way to do this.

This would be great because then I could do the comparison based operators such as UNION, EXCEPT etc. on such queries. The netxt column is not comparable so it fails when using those operators.

My current idea:

Create a function to build the query as dynamic sql. Something similar to this: http://lotsacode.wordpress.com/2010/03/23/sql-server-ntext-cannot-be-selected-as-distinct/

Is there a better way?

Thanks for your input!

Answer 1

NTEXT will be removed from future versions of SQl-Server anyway (along with Image and text), so why not just bite the bullet and change your columns to NVARCHAR(MAX)? It may be costly once, but it will probably be worth it:

ALTER TABLE dbo.T ALTER COLUMN NTextColumn NVARCHAR(MAX) NULL; -- OR NOT NULL

You can generate and execute the script for an entire database using this:

DECLARE @SQL NVARCHAR(MAX) = 
    (   SELECT  'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + 
                                QUOTENAME(OBJECT_NAME(object_id)) + 
                                ' ALTER COLUMN ' + QUOTENAME(Name) + 
                                ' NVARCHAR(MAX) ' + 
                                CASE WHEN is_nullable = 0 THEN 'NOT' ELSE '' END + 
                                ' NULL;' + CHAR(13) + 'GO' + CHAR(13)
        FROM    sys.columns
        WHERE   system_type_id = 99 --NTEXT
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');

EXECUTE sp_executesql @SQL;