ASP.NET Tricks

Below I’ll describe a problem with views that is raised when view related tables are changed.

Problem: When structure of a table is changed, all views that use “SELECT *” from this table must be refreshed manually. Otherwise the changes do not affect the views. Below are the steps to reproduce this behavior:

1. Create a table T1 with columns C1 and C2
2. Create a view V1 with body “SELECT * FROM T1”
3. Execute “SELECT * FROM V1”. The resultset has columns C1 and C2
4. Modify the table T1 by adding a new column C3
5. Execute “SELECT * FROM V1”. The resultset has columns C1 and C2. Column C3 does not appear in the resultset

Solution: The following script refreshes all views in a database, so you can be sure they are updated when table changes are made. If it is run after step 4) the resultset in step 5) will have three columns C1, C2 and C3.

DECLARE @tblTmpViews TABLE (view_name varchar(100))
DECLARE @view_name nvarchar(100)INSERT  @tblTmpViews
   SELECT [name] FROM sysobjects WHERE xtype='V'

DECLARE crsrViews CURSOR FOR
  SELECT view_name FROM @tblTmpViews

OPEN crsrViews
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
  FETCH NEXT FROM crsrViews INTO @view_name

  WHILE @@FETCH_STATUS = 0 BEGIN
    -- This is executed as long as the previous fetch succeeds.
    EXEC sp_refreshview @view_name
    FETCH NEXT FROM crsrViews INTO @view_name
  END

CLOSE crsrViews
DEALLOCATE crsrViews

Existing problem: The same issue exists for user-defined functions. Unfortunately I couldn’t find a solution. If someone finds it, please post here.