How to rebuild view in SQL Server 2008

Question

There is a view in my DB that someone defined with a * from one table. I just added a new column to that table and I want the view to reflect the new column. Besides re-executing the view creation script, is there another way to rebuild the view? I am looking for something similar to how sp_recompile will recompile a stored procedure (or more accurately flag it to be compiled next time it is called).

Update: On a long shot I tried calling sp_recompile on the view and while the call worked, it didn't rebuild the view.

Update 2: I would like to be able to do this from a script. So the script that adds the columns to the table could also update the view. So like I said, something similar to sp_recompile.

Answer 1

Right-click on the view and choose Refresh from the popup menu?

Answer 2

I believe what you're looking for is

sp_refreshview [ @viewname = ] 'viewname'

Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

http://technet.microsoft.com/en-us/library/ms187821.aspx

Answer 3

As well as Cory's answer, you could define it properly using schemabinding and the full column list.

CREATE VIEW MyView
WITH SCHEMABINDING
AS
SELECT
    col1, col2, col3, ..., coln
FROM
    MyTable
GO