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.

• Thom A: This is going to be lines like QUOTENAME(OBJECT_SCHEMA_NAME( (SELECT ID FROM #chunkOfObjects)) ) As you appear to have more than 1 row in #chunkOfObjects.
• satcha: @Larnu exactly thats my issue. if someone can help to fix it. i means how can i modify by code to select ID from my #chunckobject one by one
• Thom A: YOu need to add a WHERE. Presumably on the object_id.
• Thom A: Side note, you are altering all your ntext, image and text colums to a varchar(MAX). That could cause significant data loss. You should be using nvarchar, varbinary and varchar data types respectively.
• satcha: thanks for the note, i will change my code

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;

• satcha: Thanks for your help. Now i saw there is no update columns, i have to update column after convert. and no chunk table. i have the large tables so i have to use the chunk tables.
• Thom A: What is a "chunk table"?
• satcha: select some recors to update from original table to the temp table like what i did : SELECT top(10) ID, column_id, tableName, columnName, isNullable FROM #objectsToUpdate;
• Thom A: "i update column to itself" Why @satcha ? That is pointless other than to waste time. See my prior comment. With respect, I see no reason to add redundant and unperformant code to my solution.
• Charlieface: @satcha If they are more than 8k you definitely should not care, because nvarchar(max) more than 8k is also stored off-row so there will be no change at all. And I suggest writing against that note "not necessary"