Query that shows actual collation, changed collation and query for change column collation. Special Thanks to Betsy Alanis for this query.

ChangeCollationQuery.sql

/* 
   Query that shows actual collation, changed collation and query for change column collation. 
   Special Thanks to Betsy Alanis for this query.
*/

DECLARE @collation NVARCHAR(64) 
SET @collation = 'Modern_Spanish_CI_AS' 
 
SELECT COLLATION_NAME [Actual Collation], @collation [Destination Collation], 
     'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' + 'ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE + '(' + 
		      CASE CHARACTER_MAXIMUM_LENGTH 
		      WHEN - 1 THEN 'MAX' 
		      ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) 
		      END + ') ' + 'COLLATE ' + @collation + ' ' + 
		      CASE 
		      WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' 
		      ELSE 'NULL' END AS [Alter Column Collation Query]
	   FROM INFORMATION_SCHEMA.COLUMNS
	   WHERE (COLLATION_NAME IS NOT NULL) 
	     AND (COLLATION_NAME <> @collation) 
	     AND (TABLE_NAME NOT LIKE '%VW%')