CODEWITHSUNDEEP
sqlsql-server
unnknown
unnknown

Reputation: 1775

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

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!

Upvotes: 4

Views: 7337

Answers (3)

GarethD
GarethD

Reputation: 69759

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;

Upvotes: 7

Daniel Mitchell
Daniel Mitchell

Reputation: 57

Here is my modified version of GarethD's answer above. Had issues with SQL not finding some tables, so I used sys.tables joined with sys.columns. Also, the is_nullable line was incorrect (if the field is not nullable, then you set it to NOT NULL).

DECLARE @SQL NVARCHAR(MAX) = ' ';

SELECT  @SQL = @SQL + ' ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(sys.columns.object_id)) + '.' + 
                        QUOTENAME(OBJECT_NAME(sys.columns.object_id)) + 
                        ' ALTER COLUMN ' + QUOTENAME(sys.columns.Name) + 
                        ' NVARCHAR(MAX) ' + 
                        CASE WHEN is_nullable = 0 THEN 'NOT NULL' ELSE '' END 

FROM    sys.Tables
inner join sys.columns on sys.tables.object_id = sys.columns.object_id
WHERE   sys.columns.system_type_id = 99 ; --NTEXT 

EXECUTE sp_executesql @SQL;
GO

Upvotes: 1

marc_s
marc_s

Reputation: 754478

I used this cursor (and here, there's no set-based alternative, I'm afraid) to do just that:

DECLARE TableCursor CURSOR FAST_FORWARD 
FOR
    SELECT 
        t.Name,
        c.name,
        c.is_nullable, 
        typ.user_type_id
    FROM 
        sys.columns c 
    INNER JOIN 
        sys.tables t ON c.object_id = t.object_id
    INNER JOIN 
        sys.types typ ON c.system_type_id = typ.system_type_id
    WHERE   
        typ.name IN ('text', 'ntext')    -- user_type_id: text = 35, ntext = 99 

DECLARE @TableName sysname, @ColumnName sysname, @IsNullable BIT, @TypeID INT

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName, @IsNullable, @TypeID

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @Stmt NVARCHAR(999)

    SET @Stmt = 'ALTER TABLE dbo.[' + @TableName + '] ALTER COLUMN [' + @ColumnName + '] ' + 
              CASE @TypeID 
                WHEN 35 THEN ' VARCHAR(MAX) '
                WHEN 99 THEN ' NVARCHAR(MAX) '
              END  + 
                CASE WHEN @IsNullable = 1 THEN 'NULL' ELSE 'NOT NULL' END

    PRINT @Stmt
    EXEC (@Stmt)

    FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName, @IsNullable, @TypeID
END

CLOSE TableCursor
DEALLOCATE TableCursor

I simplified my code a bit by assuming that all my tables are in the dbo schema - if that's not the case for you, you'd have to include the schema from the sys.schema catalog view, too.

Running this code will turn all text into varchar(max) and all ntext into nvarchar(max) once and for all, and all your issues with text/ntext are gone forever! :-)

Upvotes: 4

Related Questions