March 24, 2005 at 9:03 am
We have several dependent views in our system. When we change underlying table, what's the best way to refresh these views?
For example: view1 references view2 which references table 1. Table 1 is changed.
I understand that sysdepends is not 100% reliable.
So instead of trying to figure out all the dependencies, if I know the dependency tree is 3 levels deep at most, can I just blindly go through all the views and issue sp_refreshview on multiple(3) passes?
In theory, the third time through all the views should be upto date, right?
March 27, 2005 at 12:35 am
sp_refresh view can be called in any order if you don't care about the integrity of the sysdepends contents. The order is only critical for creating the views, not refreshing them.
sp_refreshview will update sysdepends contents for a view, while also removing contents for other objects dependent on this view. A very frustrating side affect.
The only way around this is to build your own dependcy table that you can populate from sysdepends after calling sp_refreshview for each view. Yes you can rely on sysdepends for a view after you have called sp_refreshview on it. You just can't trust it for any other object.
Here's the basic strategy:
Loop through each view doing
begin
call sp_refreshview
copy results for view in sysdepends to your own table
end
After looping through each view and extracting/collecting the sysdepends results you can then call sp_refreshview again in the desired order if you want sysdepends to be accurate. I would just not bother as it is something you can never rely on long term. I just use the strategy above to extract the dependcies whenever I need to.
Hope this helps. It is a strategy we have used successfully for 7 levels of view nesting. I can give you more detail, should you require it.
Regards
Pete
November 4, 2009 at 10:48 pm
can you give example code ?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply