Database Text to NVARCHAR

/*

This time the problem we faced was: passed all existing TEXT fields in a database field NVARCHAR (MAX).

EASY, enjoy!

En esta oportunidad el problema a resolver era: pasar todos los campos TEXT existentes en una base de datos en campos NVARCHAR(MAX).

Sensillo, enjoy!

Nota: Este script puede dañar la integridad de la base, cualquier daño que provoque será bajo su responsabilidad .

Note: This script can damage the integrity of the base, causing damage thatwill be your responsibility.

*/

DECLARE @CMD VARCHAR(255)

DECLARE @TABLENAME VARCHAR(255)

DECLARE @COLUMNNAME VARCHAR(255)

--Well here declare a cursor, only that

DECLARE Table_cursor CURSOR FOR

--Make the cmd line to be executed

SELECT

'ALTER TABLE ' + O.NAME + ' ALTER COLUMN ' + C.NAME + ' NVARCHAR(MAX) NULL' AS CMD,

O.NAME AS TABLENAME,

C.NAME AS COLUMNNAME

FROM

SYS.OBJECTS AS O

INNER JOIN

SYS.COLUMNS AS C

ON (C.OBJECT_ID = O.OBJECT_ID)

INNER JOIN

SYS.TYPES AS T

ON( T.SYSTEM_TYPE_ID = C.SYSTEM_TYPE_ID )

WHERE

TYPE = 'U' AND C.SYSTEM_TYPE_ID = 35

--Open the cursor

OPEN Table_cursor

--Fetch the first

FETCH NEXT FROM Table_cursor

--Use the field values in the declared vars

INTO @CMD, @TABLENAME, @COLUMNNAME

--LOOP NOT EOF

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT REPLICATE('=',255)

PRINT 'TABLE:' + @TABLENAME

PRINT 'COLUMN:' + @COLUMNNAME

PRINT ''

PRINT 'EXECUTING => ' + @CMD

EXEC (@CMD) --here execute the command

PRINT 'DONE '

--Move next :P

FETCH NEXT FROM Table_cursor

INTO @CMD, @TABLENAME, @COLUMNNAME

--End of cursor

END

--Close an Deallocate cursor

CLOSE Table_cursor

DEALLOCATE Table_cursor

Print replicate('=',255)