Script to create views so a custom collation can be added to varchar columns

collation_views.sql

SELECT
SchName,
tblName,
'CREATE view [' + SchName + '].[vw_' + tblName + '] as SELECT  ' + STRING_AGG(colname + ' '+ 
case coltype
when 'varchar' then ' COLLATE Latin1_General_100_CI_AI_SC as ' + colname  
else '' 
end 
  , ', ') + ' from [' + SchName + '].[' + tblName + '];' AS DDLScript
FROM (
select tbl.object_id, convert(varchar, sc.name) SchName, convert(varchar, tbl.name) tblName , c.column_id colid,  convert(varchar,c.name) colname,  convert(varchar,t.name) as coltype,  convert(varchar,c.collation_name) collation_name
from sys.columns c
    join sys.tables tbl on tbl.object_id=c.object_id
    join sys.types t on t.user_type_id = c.user_type_id
	inner join sys.schemas sc on  tbl.schema_id=sc.schema_id
    left join sys.default_constraints dc on c.default_object_id =dc.object_id and c.object_id =dc.parent_object_id) a
GROUP BY SchName, tblName;