Post reply

Can you Update Multiple Views in one go?

  • September 2, 2011 at 7:35 am

    #256704

    Hi All,

    I have a number of views pointing to a particular database but I now want to point them at a test copy of that database.

    In SQL2000 it was possible to update the system table and do a Replace on the database names (not recommended I know) but as updating system tables isn't supported in SQL2005 and it is difficult/messy to get around this can anyone tell me of any way to do this in 2005?

    I have 50+ views referencing mydb.tablename* and want to set them all to look at mytestdb.tablename*

    Thanks!

  • September 2, 2011 at 8:11 am

    #1376454

    you'll have to script each view out and issue teh ALTER VIEW statement for each;

    but you can use the metadata to find them and script them out from there:

    select * from sys.sql_modules where definition like '%mydb.dbo.tablename%'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 2, 2011 at 8:32 am

    #1376465

    Cheers Lowell, that allowed me to do it a good bit quicker than manually changing each one, thanks

  • September 2, 2011 at 8:38 am

    #1376472

    paul88 (9/2/2011)

    Cheers Lowell, that allowed me to do it a good bit quicker than manually changing each one, thanks

    glad i could help!

    you can try to get fancy, and do something like this to try and script it out for you:(make sure you do this in text mode, not grid mode:

    SELECT

    REPLACE(

    REPLACE(definition,'CREATE VIEW','ALTER VIEW'),

    'mydb.dbo.tablename','mytestdb.dbo.tablename'

    ) + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)

    from sys.sql_modules where definition like '%mydb.dbo.tablename%'

    it might get you 90% of the way there or more;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 2, 2011 at 9:20 am

    #1376506

    Even better, thanks for your help!

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

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