SQL Server INFORMATION_SCHEMA.VIEW_COLUMN_USAGE not updated on table update

Question

I'm trying to learn more about SQL Server, and I came across something I don't understand. I have a SQL Server 2008 database (the free version). I have a view that I defined using a wildcard, like this:

Select * from ApplicantApplications

When I checked the system view "INFORMATION_SCHEMA.VIEW_COLUMN_USAGE", each of the columns from the view I defined were there.

Later, I updated the ApplicantApplication table and added a field "TestValue"

When I looked back at the system view "INFORMATION_SCHEMA.VIEW_COLUMN_USAGE", the new column that I added to the table was not there. The view that I defined has the new column in it.

Do I need to do something special to make that column appear in the system view? I thought it would get updated automatically.

Thanks!

This is one of the big reasons that you should NOT use "`*`" indiscriminately, especially in Views. — RBarryYoung, Nov 02 '12 at 23:16

score 0 · Accepted Answer · answered Nov 02 '12 at 22:07

View needs a recompile, EXEC sp_refreshview Viewname. years ago I wrote a script for this purpose

Declare @Name varchar(200)
Declare @SQL varchar(300)
DECLARE VCalc CURSOR FOR  Select name from sysobjects
OPEN VCalc
FETCH Next FROM VCalc INTO @Name
WHILE (@@FETCH_STATUS = 0)
    BEGIN
    Select @SQL='if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].'+@Name+''') and OBJECTPROPERTY(id, N''IsView'') = 1) EXEC sp_refreshview '+@Name
    Print @SQL
    Exec (@SQL)
    FETCH Next FROM VCalc INTO @Name
    END
CLOSE VCalc
DEALLOCATE VCalc

Will it recompile automatically after a set time, or will I need to recompile the views every time I do an update like this? — user1304444, Nov 03 '12 at 00:24

SQL Server INFORMATION_SCHEMA.VIEW_COLUMN_USAGE not updated on table update

1 Answers1