Automatically create views based on one view with each value in one column

Transact-SQL
, ,
1

Their is one view. In that view, one of the column has region column where it has different region names almost 150. I need to create a view for each region name. So I can create 150 views but what if want to do any modifications. So is thier any feature or something like that where I can automatically create views based on main view. so that if needed i can change the main view, where it effects other views.

2

Why in the world do you want to do that? what is your aim?

3

I dont know. I got a task to do that. is their any way to do that?

4

you can do it but not recommended. it will bite your a$$ later on

declare @viewcreator varchar(max)
create table mythicallands(regionname nvarchar(255))
DECLARE @regionname VARCHAR(255)

insert into dbo.mythicallands
select 'Agraria' union
select 'Kabulstan' union
select 'Winterfell' union 
select 'Vale' union
select 'Stormlands' union
select 'Reach' union
select 'Westerlands' union 
select 'Axum' union 
select 'Iron Islands' union
select 'Dorne—they'  

DECLARE mythicalregions CURSOR FOR  
SELECT replace(replace(regionname, ' ', '_'), '—','_')
  FROM mythicallands
  

OPEN mythicalregions;  
FETCH NEXT FROM mythicalregions into @regionname;  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
		select @viewcreator = '
		if exists(select 1 from sys.views where name = ''vw_' + @regionname + ''')
			begin
				drop view vw_' + @regionname + '
			end'
			
		exec (@viewcreator);

		select @viewcreator=	'create view dbo.vw_' + @regionname + '
			as
			select *
			  from dbo.mythicallands
			  where regionname = ''' + @regionname + '''
		
		'
		
		PRINT @viewcreator

		exec (@viewcreator);

      FETCH NEXT FROM mythicalregions into @regionname;  
   END;  
CLOSE mythicalregions;  
DEALLOCATE mythicalregions;  
GO  
drop table mythicallands
5

it will bite your a$$ later on? I dont understand this?

6

you will encounter a lot of problems doing this dynamic creation of views. what will be using these views? since they are dynamic, whatever needs to call them will also need to do some dynamic stuff. basically this is a very bad idea