declare @TableName nvarchar(512)
declare @SQLText nvarchar(4000)
declare @ColumnName nvarchar(256)
declare @CharacterMaxLen nvarchar(100)
declare @datatype sysname
declare @IsNullable nvarchar(10)
declare @CollationName nvarchar(256) = N'SQL_Latin1_General_CP1_CI_AS'




declare MyTableCursor CURSOR FOR
select TABLE_NAME from INFORMATION_SCHEMA.TABLES 


OPEN MyTableCursor


FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE MyColumnCursor Cursor
		FOR 
		SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE from information_schema.columns
			WHERE table_name = @TableName AND  (Data_Type LIKE '%char%' OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
			ORDER BY ordinal_position 
		Open MyColumnCursor


		FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, @CharacterMaxLen, @IsNullable
		WHILE @@FETCH_STATUS = 0
			BEGIN
			SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' + @DataType + '(' + 
			CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END + ') COLLATE ' 
			+ @CollationName + ' ' + CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
			PRINT @SQLText 


		FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, @CharacterMaxLen, @IsNullable
		END
		CLOSE MyColumnCursor
		DEALLOCATE MyColumnCursor


FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor