Post reply

sp_refreshview on indexed views

  • November 30, 2020 at 4:17 pm

    #3815204

    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

     

  • December 1, 2020 at 5:10 pm

    #3815647

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

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

  • December 1, 2020 at 8:03 pm

    #3815771

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 2, 2020 at 10:11 am

    #3815964

    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?

     

  • December 2, 2020 at 3:10 pm

    #3816064

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 3, 2020 at 6:30 pm

    #3816639

    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 5 (of 5 total)

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