Converter ntext em nvarchar(max)

SELECT
o.name AS table_name,
c.name AS column_name,
(
'alter table [' + o.name + '] ' +
'alter column [' + c.name +'] ' +
case when c.system_type_id = TYPE_ID('text') then 'varchar(max)' else 'nvarchar(max)' end + ' ' +
case when c.is_nullable = 1 then 'null' else 'not null' end + ';' + char(10) +
'UPDATE [' + o.name + '] set [' + c.name + '] = [' + c.name + '];'
+ char(10) +char(13) ) cmdsql
INTO
#tmp
FROM sys.columns c
left join sys.objects o on (c.object_id = o.object_id)
where
o.type = 'U' and o.is_ms_shipped = 0 and c.system_type_id = TYPE_ID('ntext') or c.system_type_id = TYPE_ID('text')

while exists(select null from #tmp)
begin

set nocount on
declare @sql nvarchar(max)

select top 1 @sql = cmdsql from #tmp
set nocount off
exec sp_executesql @sql
set nocount on

delete from #tmp where cmdsql = @sql
end
drop table #tmp