SQL Server 2008 - sp_refreshview bombing out on some views

Question

I've inherited a fairly substantial project, which makes extensive use of SQL Server (2005 and 2008) views.

One step in the build process is the call the sp_refreshviews system stored procedure, to make sure, no changes on any tables have broken our views. This works fine .... except for about three or four (out of 200+) views....

With those, it just bombs out - gives odd error messages like

Msg 15165, Level 16, State 1, Procedure sp_refreshsqlmodule_internal, Line 55
Could not find object 'vYourViewNameHere' or you do not have permission.

which is dead wrong - that view does exist, and I definitely can select from it.

I cannot seem to find any good concise information about why this happens, what triggers it... any ideas? Is there anything I could do to detect such problematic views? Can I change their definitino so that they'd be refreshable again?

Update: I logged a bug report on Microsoft Connect for this - if you agree this seems odd and needs to be fixed, please vote for it!

https://connect.microsoft.com/SQLServer/feedback/details/676728/sp-refreshview-crashes-with-misleading-error-on-views-with-schemabinding

Answer 1

I noticed in the comments you mention it has SCHEMABINDING. I can almost guarantee that is the issue. Books online specifically says this is for use on non-schema bound views.

A scheme-bound view wouldn't allow a breaking change to occur so updating the meta-data is un-necessary. You can safely skip it.

You can identify all the schemabound views like this:

SELECT * FROM sys.views WHERE OBJECTPROPERTY(object_id, 'IsSchemaBound')=1