What is the difference between sp_refreshView and sp_recompile

Posted on 2005-04-12
Last Modified: 2008-01-09
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
Question by:JohnBPrice
    LVL 28

    Accepted Solution

    You should use sp_refreshview.  sp_recompile is only for triggers and stored procedures.
    LVL 16

    Author Comment

    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."


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now