Post reply

Automation of update to views?

  • May 12, 2010 at 1:13 pm

    #221090

    We have a DB with several hundred views. I need to make a text change across about 400 views. I can get the list of views that need to be changed through Information_Schema.Views, but I can't update that way.

    Is there a way to modify the definition of a view in TSQL where I could use the list from Information_Schema.Views to drive a TSQL script to make the text change in all the target views?

    Any assistance would be greatly appreciated.

     

  • May 12, 2010 at 1:29 pm

    #1166496

    The only way I could think to do it would be to script all the veiws with drop and then do a search and replace assumig that you are fairly confident that the replace would be unique to your text change.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • May 12, 2010 at 3:20 pm

    #1166555

    Sorry miss-read your question ...

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 13, 2010 at 5:25 am

    #1166772

    I had thought about scripting all of them. With over 400 it's not my favorite approach. I didn't know if anyone had a way to actually update the View_Definition (whereever it is stored) that you can read from Information_Schema.Views...

    Thanks.

     

  • May 13, 2010 at 5:25 am

    #1166773

    bitbucket --

    Huh?

     

  • May 13, 2010 at 5:36 am

    #1166776

    rschaeferhig (5/13/2010)

    I didn't know if anyone had a way to actually update the View_Definition (whereever it is stored) that you can read from Information_Schema.Views...

    Nope. System tables cannot be updated.

    Script, edit, run or buy a tool like Redgate's SQLRefactor to do the necessary changes for you, or write your own tool to script, modify and run.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 13, 2010 at 5:39 am

    #1166777

    Thanks Gail.

    I'm not going to get away with buying a tool as much as I'd like to get SQL Refactor (we have three other Red Gate tools). Looks like scripting and mass update are my options.

     

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

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