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