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

DECLARE @SQL NVARCHAR(MAX) = '';

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

SP_EXECUTESQL @SQL;

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

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