9. srpna 2012 22:06
I am trying to refresh all of my views on a database using this stored procedure. I am using SSMS 2008 R2:
CREATE Procedure [dbo].[sp_RefreshAllViews] as begin declare @viewName sysName declare cViews cursor for select Object_Name(o.id) from sysObjects o where OBJECTPROPERTY(o.id, N'IsView') = 1 set nocount on -- loop over all Database views (in current database) open cViews fetch next from cViews into @viewName while @@fetch_status = 0 begin -- exec refresh on view def. exec sp_refreshView @viewName print 'Refreshing View ' + @viewName + '...' -- go to next view definition fetch next from cViews into @viewName end -- close and deallocate cursor close cViews deallocate cViews set nocount off end
But after refreshing about 100 views, I get this error:
Refreshing View workgroups_notification_view... Msg 208, Level 16, State 6, Procedure sp_refreshsqlmodule_internal, Line 75 Invalid object name 'dbo.vSchoolsAttended'.The strange thing is that I do not see any views or tables in this database with this name! The only similarly named view is called dbo.yvSchoolsAttended. Also, there is no table with this name. How can I find this invalid object name? Like is there additional info I can print from my stored proc above?
9. srpna 2012 22:19Moderátor
Hi Ryan, my guess would be that in one of those views you are referencing a table that no longer exists, thus it fails when trying to refresh. A few options, but the easiest is probably to use OBJECT_DEFINITION.
SELECT * FROM sys.views WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%dbo.vSchoolsAttended%'
Sam Lester (MSFT)