Generate views to correct the column collation on a target DB's tables automatically

CREATE VIEW [dbo].[vw_sales_salesorders]
AS 
SELECT 
  saleID, 
  firstName COLLATE SQL_Latin1_CP1_CI_AS firstName, -- TARGETDB is Latin1_General_100_CI_AS 
  lastName COLLATE SQL_Latin1_CP1_CI_AS LastName, -- TARGETDB is Latin1_General_100_CI_AS 
  salesAmount 
FROM [TARGETDB].[sales].[salesorders]

/*
  Create this proc in the DB you will be creating these views.

  @targetDB: The DB your new views will be targetting
  @collation: The target collation you want to change the columns to if they do not match
  @prefix: Set to something like 'vw'
*/CREATE OR ALTER proc [dbo].[usp_refreshViewsToDB]
  @targetDB nvarchar(100), @collation nvarchar(100), @prefix nvarchar(10)
as
  set nocount on
  declare
      @tablename nvarchar(512),
      @viewname nvarchar(512),
      @query nvarchar(max) = '',
      @colID int,
      @maxColID int,
      @prefixfilter nvarchar(11)

  set @prefixfilter = @prefix + '_%'  -- Will append a _, so no need to declare it in @prefix

  -- Part 1: Drop the existing views that start with the prefix you have in the argument.
  -- Please note, if there are other views in this DB that start with the same prefix, they WILL be dropped
  --   so make sure these special views are prefixed uniquely

  select name [ViewName] into #viewsToDrop from sys.views where name like @prefixfilter
  
  SELECT TOP 1 @viewname = viewname from #viewsToDrop
  while (@@ROWCOUNT > 0)
  begin
    set @query = @query + 'DROP VIEW ' + @viewname + '; '
    delete #viewsToDrop where ViewName = @viewname
    SELECT TOP 1 @viewname = ViewName from #viewsToDrop
  end
  print (@query)
  -- exec (@query) -- uncomment when you're ready to run for real

  -- Part 2: Populate the table list
  create table #RawData
  (
    TableName nvarchar(100),
    ColumnName nvarchar(100),
    Column_ID int,
    DataType nvarchar(100),
    Collation_Name nvarchar(100),
    Create_Date datetime,
    Type_Desc nvarchar(100)
  )

  set @query ='
  ;with COL as
  (
  select S.name + ''.'' + T.name [Tablename], C.name [ColumnName], C.column_id, ST.name [DataType],
    case
      when C.collation_name is null then NULL
      else C.collation_name
    end [collation_name], T.create_date, T.type_desc
  from ' + quotename(@targetDB) + '.sys.columns as C
    inner join ' + quotename(@targetDB) + '.sys.tables T on T.object_id = C.object_id
    inner join ' + quotename(@targetDB) + '.sys.schemas S on S.schema_id = T.schema_id
    inner join ' + quotename(@targetDB) + '.sys.types ST on ST.user_type_id = C.user_type_id
  )
  insert into #RawData  SELECT * FROM COL'
  exec(@query)

  -- Part 3: Go through each table above and create a corresponding view to match it
  -- Cannot be done as one big because each CREATE VIEW must be unique, 
  --   as GO cannot be put in an EXEC statement, so each table must be processed separately
  select distinct TableName into #TableNames from #RawData
  set @query = ''

  SELECT TOP 1 @tablename = Tablename from #TableNames order by Tablename
  while (@@rowcount > 0)
  begin
    set @query = 'CREATE VIEW ' + @prefix + '_' + replace(@tablename, '.', '_') 
      + char(10) + 'AS' + CHAR(10) + 'SELECT ' + CHAR(10)
    select @maxColID = max(column_id) from #RawData where Tablename = @tablename
    set @colID = 1
    while (@colID <= @maxColID)
    begin
      SELECT @query = @query + '  ' + quotename(columnName) +
        CASE
          -- this assumes IS NULL matches your desired @collation, 
  -- and is not some third collation
          when collation_name IS NOT NULL THEN ' COLLATE ' + @collation + ' ' + quotename(columnName)
          ELSE ''
        end
      from #RawData where column_id = @colID and Tablename = @tablename
      if (@colID < @maxColID) set @query = @query + ',' + char(10)
      set @colID = @colID + 1
    end
    set @query = @query + char(10) + 'FROM ' + @targetDB + '.' + @tablename 
      + ' WITH (NOLOCK)' + char(10)
    print (@query)
    -- exec (@query) -- uncomment when you're ready to run for real

    delete #TableNames where Tablename = @tablename
    SELECT TOP 1 @tablename = Tablename from #TableNames order by Tablename
  end

  drop table #RawData
  drop table #TableNames
GO