sp_refreshview on indexed views

  • Hi

    I have to increase the size of a field in a table. I've listed all the views that use this table so I can run sp_refreshview on these.

    I'm left with a couple of schema bound views that use the underlying table, but not the changed field otherwise it wouldn't have allowed me to change the field in the first place.

    I cannot use sp_refreshview on these views.

    Should I drop them and re-create them?

    Alter view will drop the indices defined on them..

    Thanks

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • I think you will have to drop them and re-create them (once/if sp_refreshview doesn't work).

    Script them out, being sure to include any permissions or other system table / metadata entries related to them, such as extended properties.

    You might want to consider switching to using a synonym for the view, so that it's somewhat easier later to change the view itself.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks Scot

    I've scripted ALTER statements on the indexed views so I get to keep permissions. I've also included index creation as running the ALTER drops the indices.

    I've not used synonym - how would that help?

     

  • I think a synonym would allow you to create a completely new view without affecting the current one.  Then, when you had the new view fully tested out, with all permissions, etc., added, you could put the new view in place just by dropping the synonym and recreating it.

    CREATE SYNONYM dbo.my_prod_view FOR dbo.my_prod_view_20201113;

    --need for new view arises on 2020-12-01

    CREATE VIEW my_prod_view_20201201 AS ...

    Do all the testing to get the new view ready, then, after all testing, do this:

    CREATE SYNONYM dbo.my_prod_view FOR dbo.my_prod_view_20201201;

    None of the code using the synonym name ever has to be changed in any way, it just now automatically points to the new view.

    It was just a possibility, not a "must do" recommendation.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks Scott

    I will not use this time, but I'm going to file this away - I can see this being useful sometime.

     

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply