Invalid object name during View refresh

Question

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?

---

Ryan D

Answer 1

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%'

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.