Sql-server – Convert all ntext columns to nvarchar(max)

Question

I'm trying to convert all ntext columns in my database to nvarchar(max).

This is the code:

        DECLARE @command NVARCHAR(MAX);
        DECLARE @numberOfRecordsToUpdate as int;
        DECLARE @numberOfRowsUpdated as int;
        DECLARE @totalNumberOfRecordsToUpdate as int;
        DECLARE @object_id INT,
                @column_id INT,
                @SchemaName varchar(250),
                @tableName varchar(250),
                @columnName SYSNAME,
                @isNullable BIT,
                @System_Type_Id INT,
                @lenght INT

        CREATE TABLE #chunkOfObjects(ID int, column_id INT, tableName varchar(250), columnName          SYSNAME, isNullable BIT);

        CREATE TABLE #objectsToUpdate(
                ID int,
                column_id INT,
                SchemaName varchar(250),
                tableName varchar(250),
                columnName SYSNAME,
                isNullable BIT,
                System_Type_Id INT,
                lenght INT);

        INSERT INTO #objectsToUpdate 
                        SELECT c.object_id, column_id, (s.name), (o.name), c.name, c.is_nullable, c.system_type_id, DATALENGTH(c.name) as lenght
                        FROM sys.all_columns AS c
                        INNER JOIN sys.objects AS o ON c.object_id = o.object_id
                        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
                        WHERE o.type = 'U' AND  c.system_type_id in ('99','35','34') order by (o.name) ;

        DECLARE col_cursor CURSOR FAST_FORWARD FOR SELECT * FROM #objectsToUpdate;
        OPEN col_cursor;
        FETCH NEXT FROM col_cursor INTO @object_id, @column_id, @SchemaName, @tableName, @columnName, @isNullable, @System_Type_Id, @lenght;
        WHILE @@FETCH_STATUS = 0
        BEGIN

                    SELECT @numberOfRecordsToUpdate = count(ID) FROM #objectsToUpdate;
                    SELECT @totalNumberOfRecordsToUpdate = @numberOfRecordsToUpdate;
                    PRINT CURRENT_TIMESTAMP;
                    PRINT 'Cleaning';
                    WHILE (@numberOfRecordsToUpdate > 0)
                        BEGIN
                            INSERT INTO #chunkOfObjects (ID, column_id, tableName, columnName, isNullable  ) SELECT top(10) ID, column_id, tableName, columnName, isNullable FROM #objectsToUpdate;
                            DELETE FROM #objectsToUpdate WHERE ID in ( SELECT ID FROM #chunkOfObjects );
                            BEGIN TRANSACTION tr;                       
                                SELECT @command =
                                    'ALTER TABLE '
                                    + QUOTENAME(OBJECT_SCHEMA_NAME( (SELECT ID FROM #chunkOfObjects)) )
                                    + '.' + QUOTENAME(OBJECT_NAME( (SELECT ID FROM #chunkOfObjects)) )
                                    + ' ALTER COLUMN '
                                    + QUOTENAME((SELECT @columnName FROM #chunkOfObjects where))
                                    +' varchar(max)'    
                                    + CASE
                                        WHEN ( SELECT @isNullable FROM #chunkOfObjects ) = 1 THEN '' ELSE 'NOT'
                                      END
                                    + ' NULL;';
                                PRINT @command;
                                SELECT @command ='UPDATE ' +QUOTENAME(OBJECT_SCHEMA_NAME( (SELECT ID  FROM #chunkOfObjects )) ) + '.' 
                                    + QUOTENAME(OBJECT_NAME( (SELECT ID FROM #chunkOfObjects )) ) + ' SET ' 
                                    + QUOTENAME((SELECT @columnName FROM #chunkOfObjects )) + ' = '
                                    + QUOTENAME((SELECT @columnName FROM #chunkOfObjects )) 
                                PRINT @command;
                                EXEC sp_executesql @command
                                SELECT @numberOfRowsUpdated = COUNT(ID) FROM #chunkOfObjects;
                                PRINT 'Updtated: ' + CAST(@numberOfRowsUpdated as varchar(15)) + ' row(s) of ' + CAST(@totalNumberOfRecordsToUpdate as varchar(15));
                                TRUNCATE TABLE #chunkOfObjects;
                            COMMIT TRANSACTION tr;                  
                            SELECT @numberOfRecordsToUpdate = count(ID) FROM #objectsToUpdate;
                            PRINT 'Remaining ' + CAST(@numberOfRecordsToUpdate as varchar(15)) + ' row(s) ' + NCHAR(10) + '-------------';
                        END
                    PRINT CURRENT_TIMESTAMP
            FETCH NEXT FROM col_cursor INTO @object_id, @column_id, @SchemaName, @tableName, @columnName, @isNullable, @System_Type_Id, @lenght;
        END
        CLOSE col_cursor;
        DEALLOCATE col_cursor;
        DROP TABLE #objectsToUpdate;
        DROP TABLE #chunkOfObjects;

Whenever I try to run it, I get this error:

Msg 512, Level 16, State 1, Line 60
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

the subquery is when im trying to select the chuck object to alter or update table :

SELECT ID FROM #chunkOfObjects

if i use the below one :

SELECT ID FROM #chunkOfObjects where column_id =@column_id  

it take only one ID, not loop on the #chunkOfObjects table

Any help to fix this error would be great.

Answer 1

The above looks like you are overly complicating the problem. You should be able to achieve this far more easily with some simple string aggregation. I assume you are using SQL Server 2017+; if not use the "old" FOR XML PATH method:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SELECT @SQL = STRING_AGG(N'ALTER TABLE ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' ALTER COLUMN ' + QUOTENAME(c.[name]) + N' ' + 
                         CASE ct.[name] WHEN N'text' THEN N'varchar'
                                        WHEN N'ntext' THEN N'nvarchar'
                                        WHEN N'image' THEN N'varbinary'
                         END + N'(MAX) ' +
                         CASE c.is_nullable WHEN 1 THEN N'NULL'
                                            ELSE N'NOT NULL'
                         END + N';',@CRLF)
FROM sys.schemas s
     JOIN sys.tables t ON s.schema_id = t.schema_id
     JOIN sys.columns c ON t.object_id = c.object_id
     JOIN sys.types ct ON c.user_type_id = ct.user_type_id
WHERE ct.[name] IN (N'text',N'ntext',N'image');

PRINT @SQL;
EXEC sys.sp_executesql @SQL;