JohnBPrice
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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."