/*
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)
