Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: Updating View code

    Hello.

    I have a View in my DB with a list of all databases specified in it:

    SELECT * FROM DB_Name_1.dbo.TableName WITH (NOLOCK)
    UNION ALL
    SELECT * FROM DB_Name_2.dbo.TableName WITH (NOLOCK)
    UNION ALL
    SELECT * FROM DB_Name_2.dbo.TableName WITH (NOLOCK)
    ...
    ...

    Can I use UserDefinedFunction or StoredProcedure to update this code with new database?

    I want to perform it automatically and not open ("Edit") the View code each time.

    In general - can View (or StoredProcedure) code be edited automatically?

    Any help would be great.
    Thanks.

  2. #2
    Join Date
    Aug 2005
    Posts
    75
    Code:
    alter view vw_name as
    
    SELECT * FROM NEW_DB_Name_1.dbo.TableName WITH (NOLOCK)
    UNION ALL
    SELECT * FROM NEW_DB_Name_2.dbo.TableName WITH (NOLOCK)
    UNION ALL
    SELECT * FROM NEW_DB_Name_2.dbo.TableName WITH (NOLOCK)
    
    go
    if the DBs are listed somewhere in a table, you could probably wrap the above inside a stored procedure that concatenates the new view statement together via dynamic sql (yes, I know dynamic sql is the devil, but this is one scenario where it would work very well).

  3. #3
    Join Date
    Mar 2009
    Posts
    349
    do not use SELECT * in views especially when Unioning. That can break real easy.

    your original question I do not really understand. why is the same table in different databases.

    all kinds of bad here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •