How to Automatically "recompile" views
Posted on 2004-04-08
I have a SQL Server 2000 database with hundreds of views and stored procedures. Views are often nested several levels. As a very simple example I have a view called vw1 that looks like:
select tbl1.* from tbl1 inner join tbl2 on tbl1.colx = tbl2.colx
If I then add a new column to tbl1, this new column is not reflected in the vw1 view. To get the column to show up I have to open vw1 in design mode, uncheck the * in the diagram pane, and then recheck the * in the diagram pane. I have to do the same thing for every view that is base on this view if a select * is used. It seems strange to me that if a view only involves 1 table, e.g. select * from tbl1, the new column shows up automatically. With hundreds of views, it's all too easy to miss one when a new column is added to an underlying table. Is there some transact sql command or some other mechanism to automatically "recompile" all views?