locked
View referencing another database RRS feed

  • Question

  • Hi Team

       We have two database called "A" and "B" on same production server. In database "B" we have view called GetStudentData which refers table in database "A" with following definition

    create view GetStudentData as

    select * from A.dbo.StudentData

    go

    Now when we copy database "A" to "A_test" and "B" to "B_test" on different server then we want to change database view definition automatically , which refere to "A_test" instead of "A" so definition would be

    create view GetStudentData as

    select * from A_test.dbo.StudentData

    go

    to make it possible I tried with dynamic query "Exec("select * from A.dbo.StudentData") in view , but doesn't work. Is is possible to change referring database name in view based on current database name ?(if current database is "B" then referring database should be "A". if current database is "B_test" then referring database shoud be "A_test")

    thanks in advance


    Drew

    Tuesday, November 12, 2013 5:01 PM

Answers

  • Doubt it - not without dynamic sql.

    You can create scripts pointing at the tables in the other database

    if object_ID('vwMyTbl') is not null

    drop view vwMyTbl

    create view vwMyTbl

    as

    select * from <dbname>..MyTbl

    go

    do that for every table.

    Run a replace on <dbname> with the database you want to reference then run the script.

    You can then reference vwMyTbl in your database or view and it will point to the table in your other database.

    A restore will also work but you need to run the view script before using the views to reference a different database.

    • Marked as answer by drew_p Tuesday, November 12, 2013 5:46 PM
    Tuesday, November 12, 2013 5:15 PM
  • There is no way to do what you are trying to do.

    You would need to script every view, change the database name, and alter/create every view again.

    However, what I have done before is create a new database on the server called A,B which simply contains views:

    CREATE VIEW TABLEA AS 

    SELECT * FROM A_Test.TABLEA


    • Edited by Tom PhillipsEditor Tuesday, November 12, 2013 5:34 PM
    • Marked as answer by drew_p Tuesday, November 12, 2013 5:46 PM
    Tuesday, November 12, 2013 5:33 PM
    Answerer
  • nope...view does not allow dynamic sql. 

    best approach i can think is create linked server to database and used linked server name in view. 

    In case you want to move the database, just script the linked server and move them along with database and point it to appropriate database. 


    Regards Harsh

    • Marked as answer by drew_p Tuesday, November 12, 2013 5:46 PM
    Tuesday, November 12, 2013 5:43 PM

All replies

  • Doubt it - not without dynamic sql.

    You can create scripts pointing at the tables in the other database

    if object_ID('vwMyTbl') is not null

    drop view vwMyTbl

    create view vwMyTbl

    as

    select * from <dbname>..MyTbl

    go

    do that for every table.

    Run a replace on <dbname> with the database you want to reference then run the script.

    You can then reference vwMyTbl in your database or view and it will point to the table in your other database.

    A restore will also work but you need to run the view script before using the views to reference a different database.

    • Marked as answer by drew_p Tuesday, November 12, 2013 5:46 PM
    Tuesday, November 12, 2013 5:15 PM
  • There is no way to do what you are trying to do.

    You would need to script every view, change the database name, and alter/create every view again.

    However, what I have done before is create a new database on the server called A,B which simply contains views:

    CREATE VIEW TABLEA AS 

    SELECT * FROM A_Test.TABLEA


    • Edited by Tom PhillipsEditor Tuesday, November 12, 2013 5:34 PM
    • Marked as answer by drew_p Tuesday, November 12, 2013 5:46 PM
    Tuesday, November 12, 2013 5:33 PM
    Answerer
  • nope...view does not allow dynamic sql. 

    best approach i can think is create linked server to database and used linked server name in view. 

    In case you want to move the database, just script the linked server and move them along with database and point it to appropriate database. 


    Regards Harsh

    • Marked as answer by drew_p Tuesday, November 12, 2013 5:46 PM
    Tuesday, November 12, 2013 5:43 PM
  • Hello,

    Actually this is a "it depends" area. It depends on how the model is coded and needs to work. In this example, it would be a great candidate for the use of Synonyms and a linked server.

    -Sean


    Sean Gallardy | Blog | Twitter

    Tuesday, November 12, 2013 5:48 PM
  • Basically we want to keep as simple as possible. We are migrating from Sybase to Sql server. Also we do copy production data to developer databases which has different database name then production. Currently we are executing scripts to point the views to right database.  We are looking alternates to scripts if possible.

      can you provide example of synonyms and linked server ?

    thanks


    Drew

    Tuesday, November 12, 2013 5:51 PM
  • Drew,

    See below for the same code.

    CREATE DATABASE Test_A;
    GO
    
    CREATE DATABASE Test_A_2;
    GO
    
    CREATE DATABASE Test_A_3;
    GO
    
    use Test_A;
    GO
    
    CREATE TABLE Table_A
    (
    TheMessage	VARCHAR(60) NOT NULL
    );
    GO
    
    INSERT INTO Table_A(TheMessage) VALUES ('Test_A Database Table_A value.');
    GO
    
    use Test_A_2
    GO
    
    CREATE TABLE Table_A_2
    (
    TheMessage	VARCHAR(60) NOT NULL
    );
    GO
    
    INSERT INTO Table_A_2(TheMessage) VALUES ('Test_A_2 Database Table_A_2 value.');
    GO
    
    use Test_A_3
    GO
    
    CREATE TABLE Table_A_3
    (
    TheMessage	VARCHAR(60) NOT NULL
    );
    GO
    
    INSERT INTO Table_A_3(TheMessage) VALUES ('Test_A_3 Database Table_A_3 value.');
    GO
    
    use master
    go
    /* yes I used IP address because of some quirks with my test system */
    /* you should, however, use DNS (name) */
    EXEC dbo.sp_addlinkedserver @server = N'192.168.192.128', @srvproduct=N'SQL Server'
    /* put in your security preferences here */
    GO
    
    use Test_A
    GO
    
    CREATE SYNONYM [dbo].[Partner_Server] FOR [192.168.192.128].Test_A_2.dbo.Table_A_2
    GO
    
    CREATE VIEW GetViewData
    AS
    SELECT * FROM Partner_Server
    GO
    
    /* get the values */
    SELECT * FROM GetViewData
    UNION
    SELECT * FROM Table_A
    GO
    /* now, assume the database was restored to a different server */
    /* all you need to do is change the synonym definition */
    
    DROP SYNONYM dbo.Partner_Server
    GO
    
    CREATE SYNONYM [dbo].[Partner_Server] FOR [192.168.192.128].Test_A_3.dbo.Table_A_3
    GO
    
    /* using the same view, same code just changed the synonym */
    SELECT * FROM GetViewData
    UNION
    SELECT * FROM Table_A
    GO

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, November 13, 2013 2:17 PM