How to force View to change automatically when table schema changes?

I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.

And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?Is your view an indexed view?|||Originally posted by sbaru
Is your view an indexed view?

Nope, it's not an indexed view, the view joins a few tables so it's not eligible for that.

Another strange thing is that if I select Open View the results is still from the old view, but if I do Design View the output from running the SQL is correct (not the same as what I saw when I did Open View). It's only when I deliberately change a field or two in the view in Design mode will the Open View gives me the correct output. That's why I am thinking the View is still using the old, compiled execution plan until I changed something in the View. That is a pain though since I am constanly change table schema.|||You should run :

--First option
EXEC sp_refreshview @.ViewName

--Secnod option

CREATE PROCEDURE REFRESH_ALL_VIEWS
AS
DECLARE @.ViewName varchar(100)
DECLARE curViews CURSOR FOR select name from sysobjects where xtype='V'
OPEN curViews
FETCH NEXT FROM curViews INTO @.ViewName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @.ViewName
FETCH NEXT FROM curViews INTO @.ViewName
END
CLOSE curViews
DEALLOCATE curViews

GO|||Beautiful! Thank you!

How to force a recompile on the stored procs?

I want to recompile all the stored procs in the database. I've tried
sp_recompile on all of them, but it simply marks them to be recompiled.
ThanksThey don't actually get recompiled until the next time they get called.
That is how it always works.
--
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks|||DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks