Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: 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?
    Last edited by waspfish; 01-18-04 at 00:13.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Re: How to force View to change automatically when table schema changes?

    Is your view an indexed view?

  3. #3
    Join Date
    Jan 2004
    Posts
    9

    Re: How to force View to change automatically when table schema changes?

    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.
    Last edited by waspfish; 01-18-04 at 00:16.

  4. #4
    Join Date
    Jul 2002
    Posts
    63
    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

  5. #5
    Join Date
    Jan 2004
    Posts
    9
    Beautiful! Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •