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.
Why in the world do you want to do that? what is your aim?
I dont know. I got a task to do that. is their any way to do that?
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
it will bite your a$$ later on? I dont understand this?
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