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!