[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2049
  • Last Modified:

What is the difference between sp_refreshView and sp_recompile

A couple times I've had a problem where a view would get fouled up because the underlying tables are changed.  The view still works, but the data gets all fouled up.  sp_refreshView and sp_recompile should both fix this problem, which should I use?  Both?  Is there a better way to refresh views automatically when the underlying tables change?

I am thinking that I should periodically run this, but with so_refreshview or sp_recompile or both?

declare @Objname varchar(100)
declare c cursor for SELECT name from SYSobjects where xtype='V'
open c
fetch c into @ObjName
while @@FETCH_STATUS=0
      exec sp_refreshview @objname
      exec sp_recompile @objname
      fetch c into @ObjName
close c
deallocate c
1 Solution
You should use sp_refreshview.  sp_recompile is only for triggers and stored procedures.
JohnBPriceAuthor Commented:
Gotcha, thanks.  I mis-read the documentation, I saw "[@objname =] 'object' Is the qualified or unqualified name of a stored procedure, trigger, table, or VIEW in the current database.",

but I didn't read the details about "If object is the name of a table or view, all the stored procedures that reference the table or view will be recompiled the next time they are run."


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now