• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2203
  • 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
BEGIN
      exec sp_refreshview @objname
      exec sp_recompile @objname
      fetch c into @ObjName
END
close c
deallocate c
0
JohnBPrice
Asked:
JohnBPrice
1 Solution
 
rafranciscoCommented:
You should use sp_refreshview.  sp_recompile is only for triggers and stored procedures.
0
 
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."

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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