Sql Server Programming :: making views refresh themselves


	making views refresh themselves I have a view (vwParent) that inner joins with another view (vwChild). If the underlying vwChild view is modified, the vwParent still returns the old columns it originally returned. Only rerunning the vwParent view corrects the issue. I have sample sql below that clearly illustrates the issue. Hopefully someone can provide me guidance on what to do here. The sample sql illustrates the problem, but it is actually a real world scenario (problem). During application runtime the vwChild view changes frequently, the vwParent is the main view that returns all the data, and the child.* syntax is leveraged to return all the columns contained by the vwChild view. a) Is there an easy way around this problem? b) How can I rerun the vwParent view without having to copy/paste the view into a stored proc that executes a string dynamically? Is there a way to somehow use sp_helptext to get the view's string into a variable that can then be used by sp_execute? Or is there a way to make a view refresh itself without going through a bunch of convoluted steps? ============================ /* COPY/PASTE SAMPLE SQL TO ISQL TO SEE THE ISSUE / create table parent (id int, x int, y int) create table child (id int, a int, b int, c int) insert into parent (id, x, y) values (100, 1, 2) insert into child (id, a, b, c) values (100, 4, 5, 6) go create view vwChild as select id, a, b from child go create view vwParent as select x, y, child. from parent parent inner join vwChild child on child.id = parent.id go -- returns colummns x, y, child.id, child.a, child.b select * from vwParent go alter view vwChild as select id, a, b, c from child go /* Even though vwChild has been modified vwParent still returns colummns x, y, child.id, child.a, child.b! / select from vwParent go /* Rerun the vwParent view even though it hasn't changed. / alter view vwParent as select x, y, child. from parent parent inner join vwChild child on child.id = parent.id go /* Rerunning the vwParent view sql now makes it return colummns x, y, child.id, child.a, child.b, and child.c / select from vwParent go drop view vwChild drop view vwParent drop table parent drop table child On May 30, 6:25 am, jeljeljel <livermore.j@gmail.com> wrote: - Hide quoted text - > I have a view (vwParent) that inner joins with another view > (vwChild). If the underlying vwChild view is modified, the vwParent > still returns the old columns it originally returned. Only rerunning > the vwParent view corrects the issue. > I have sample sql below that clearly illustrates the issue. > Hopefully someone can provide me guidance on what to do here. The > sample sql illustrates the problem, but it is actually a real world > scenario (problem). During application runtime the vwChild view > changes frequently, the vwParent is the main view that returns all the > data, and the child.* syntax is leveraged to return all the columns > contained by the vwChild view. > a) Is there an easy way around this problem? > b) How can I rerun the vwParent view without having to copy/paste the > view into a stored proc that executes a string dynamically? Is there > a way to somehow use sp_helptext to get the view's string into a > variable that can then be used by sp_execute? Or is there a way to > make a view refresh itself without going through a bunch of convoluted > steps? > ============================ > /* COPY/PASTE SAMPLE SQL TO ISQL TO SEE THE ISSUE / > create table parent (id int, x int, y int) > create table child (id int, a int, b int, c int) > insert into parent (id, x, y) values (100, 1, 2) > insert into child (id, a, b, c) values (100, 4, 5, 6) > go > create view vwChild > as > select id, a, b from child > go > create view vwParent > as > select x, y, child. > from parent parent > inner join vwChild child on child.id = parent.id > go > -- returns colummns x, y, child.id, child.a, child.b > select * from vwParent > go > alter view vwChild > as > select id, a, b, c from child > go > /* > Even though vwChild has been modified > vwParent still returns colummns x, y, child.id, child.a, child.b! > / > select from vwParent > go > /* > Rerun the vwParent view even though it hasn't changed. > / > alter view vwParent > as > select x, y, child. > from parent parent > inner join vwChild child on child.id = parent.id > go > /* > Rerunning the vwParent view sql now makes it return > colummns x, y, child.id, child.a, child.b, and child.c > / > select from vwParent > go > drop view vwChild > drop view vwParent > drop table parent > drop table child When you change the view run sp_refreshview 'viewname' for all the views dependent on that view including that view -----------------------------------------------Reply----------------------------------------------- You will need to refresh all the VIEWs with sp_refreshview. But you missed other things as well. This is why we never use SELECT * in production code; you want an explicit column list to trap such changes. Also, unless your data deal with Volkswagens, quit using that silly "vw-" prefix and start following ISO-11179 data element naming rules.