locked
how to avoid regenerate(altering again) in sub views whenever i add new column in root views RRS feed

  • Question

  • Hi,

    I have root view and have subviews.

    i am using root view in all subviews.  i add any new column in root view and altered it,But, it is not coming in subviews unless i re altered in subviews.

    i need to regenerate(Altering again) subviews everytime if i add a new column in root view.

    how to newly added column in subviews without regenerating(Altering again) ?

    Friday, April 3, 2015 11:10 AM

Answers

  • That is how views work in SQL Server.  The column list is created a CREATE/ALTER time, not runtime.  So using SELECT * in a view does nothing.

    You can refresh the view definition by using sp_refreshview.

    https://msdn.microsoft.com/en-us/library/ms187821.aspx

    Friday, April 3, 2015 11:42 AM
  • You could also stop using nested views, which would solve both this problem and the inevitable performance issue you're going to have with them.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Friday, April 3, 2015 5:49 PM

All replies

  • That is how views work in SQL Server.  The column list is created a CREATE/ALTER time, not runtime.  So using SELECT * in a view does nothing.

    You can refresh the view definition by using sp_refreshview.

    https://msdn.microsoft.com/en-us/library/ms187821.aspx

    Friday, April 3, 2015 11:42 AM
  • wov that is great.. i can use sp_refereshview..

    But i am wondering how can i call the store procedure inside the root view..

    Friday, April 3, 2015 12:21 PM
  • There is no way to call a stored proc inside a view.  You would need to add it after you alter the view.

    Friday, April 3, 2015 5:34 PM
  • You could also stop using nested views, which would solve both this problem and the inevitable performance issue you're going to have with them.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Friday, April 3, 2015 5:49 PM