Automation of update to views?

  • 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.

     

  • 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.

  • 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]

  • 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.

     

  • bitbucket --

    Huh?

     

  • 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
  • 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