how do you sp_refreshview SchemaName.Viewname?

Question

Post reply

how do you sp_refreshview SchemaName.Viewname?

Lowell

SSC Guru

Points: 323518
More actions
August 4, 2010 at 9:48 am

#238444

was building a simple script to refresh all my views, since some columns were added to some base tables and *gasp* the views use "SELECT * FROM SOMETABLE"
yeah i know... [insert admonishments here]
anyway, some of the views are bound to a schema...[production].viewname, [lookups.viewname] etc....
the command fails, as sp_refreshview does not allow a schema:
exec sp_refreshview [lookups].[states]
exec sp_refreshview [dbo].[states]
exec sp_refreshview lookups.states
exec sp_refreshview dbo.states
exec sp_refreshview [states] --ok for dbo, but not for [lookups] schema
anyway, I cursored thru with an ALTER command for each view, so problem solved...always more than one way to tackle a problem with SQL....but in theory, how do you use sp_refreshview on a schema bound view not owned by dbo?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ronzo Hall of Fame Points: 3917 More actions · Answer 1

Muti-part naming works, but only if you submit it in single quotes, i.e.

sp_refreshview '[tms].[v_DB_ID]'

Have Fun!
Ronzo

steve.hale SSC Journeyman Points: 92 More actions · Answer 2

I have this same problem and even the '[userOfView].[viewName]' syntax does not work for me when run as "sa". Apparently sp_refreshview was modified to call sp_refreshsqlmodule, which also doesn't work for me when run as "sa", but both do work when connected as "userOfView".

sp_refreshview '[userOfView].[viewName]'

sp_refreshsqlmodule '[userOfView].[viewName]'

both return:

Msg 208, Level 16, State 6, Procedure sp_refreshsqlmodule_internal, Line 75

Invalid object name 'viewName'.

ps> this worked just fine in SqlServer2005, and SqlServer2008 (10.0.1600), but not in SqlServer2008-R2 (10.50.1600)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Does this example look like the answer or did I misunderstand your question?

USE AdventureWorks2008R2;

GO

EXECUTE sp_refreshview N'Sales.vIndividualCustomer';

I found it at the following URL:

http://msdn.microsoft.com/en-us/library/ms187821.aspx

Regards,

Welsh

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

steve.hale (5/11/2011)
I have this same problem and even the '[userOfView].[viewName]' syntax does not work for me when run as "sa". Apparently sp_refreshview was modified to call sp_refreshsqlmodule, which also doesn't work for me when run as "sa", but both do work when connected as "userOfView".
sp_refreshview '[userOfView].[viewName]'
sp_refreshsqlmodule '[userOfView].[viewName]'
both return:
Msg 208, Level 16, State 6, Procedure sp_refreshsqlmodule_internal, Line 75
Invalid object name 'viewName'.
ps> this worked just fine in SqlServer2005, and SqlServer2008 (10.0.1600), but not in SqlServer2008-R2 (10.50.1600)

Please post a new thread. You'll get more help that way anyways.