Update Dependent Views automatically?
-
I frequently find myself adding a new column to a table, then having to open and re-save all the views that reference that table (esp. if the view used
table.*
in it's select clause).This is especially cumbersome when I have 10-15 different views (some nested several layers deep). There's gotta be a easy way to automate the 'find all the dependent views and re-save them all'.
What tools or tricks have you found to do this?
-
most useful thing to do is not to use * in views, but ignoring that
...exec sp_refreshview <view>
will refresh a view
you can use this to refresh all user defined views in a db:
declare @view varchar(100)
declare view_csr cursor for
select name from sysobjects where type = 'V'
and OBJECTPROPERTY(object_id(name), 'IsMSShipped') = 0
open view_csr
fetch next from view_csr into @view
while @@FETCH_STATUS = 0
begin
exec sp_refreshview @view
fetch next from view_csr into @view
end
close view_csr
deallocate view_csr
-
This is almost exactly what I was hoping for...
Am I correct in thinking that this does not address the necessary sequencing of refreshes ?
i.e. because view #1 references view #2, I need to refresh #2 before #1. I guess I can enforce this by adding an order by and manually naming the views in 'dependency' order, but I was hoping that wasn't necessary.
I guess I could just run the whole proc several times...
-
you could order by crdate if the views were created in the correct order (ie. dependents after the views they depend upon)
is there a word for something that something else depends upon?
my minds gone a blank
-
I just recently upgraded to SQL Server 2005 as a database for a major MS-Access project. I have about 150 views. I had a macro that refreshed all of the views, but I lost that ability with 2005, since Access won't let me open views in design mode. I did a quick search on your database and found this, which is exactly what I needed. Thanks so much!!!
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply