sp_refreshview dependencies

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » sp_refreshview dependencies

sp_refreshview dependencies

Rate Topic

Display Mode

Topic Options

Author

Message

Jae Lim

Posted Thursday, March 24, 2005 9:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 28, 2005 8:27 AM
Points: 3, Visits: 1

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?

Post #169899

Peter Franta

Posted Sunday, March 27, 2005 12:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 16, 2009 1:32 AM
Points: 1, Visits: 6

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

Post #170248

rajselva

Posted Wednesday, November 04, 2009 10:48 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 04, 2009 10:47 PM
Points: 1, Visits: 0

can you give example code ?

Post #814015

« Prev Topic | Next Topic »

Permissions