Can I create view in my database server from another database server

Question

Is it possible to create view in my database server of another servers database table?

Let's say you have a database called Testing on server1 and you have another database Testing2 on server2. Is it possible to create view of Testing2's table Table2 in server1's database Testing?

Also, I am using SQL Server 2008.

Please let me know if you have any questions.

Thanks,

Solution 1

Yes, you can. First, you need to link to the other server, using something like sp_addlinkedserver.

Then you can access the data using 4-part naming. Here is an example:

create view v_server1_master_tables as
    select *
    from server1.master.information_schema.tables;

Solution 2

It is possible through linked servers. However, I wouldn't encourage you to create views based on tables from another server, as it's likely that entire table will be selected from linked server every time you use this view - optimizer may not know about this table structure to issue any filters. I've seen it at work, where nobody knew where select * from queries on large table come from that were slowing down the database, and it appeared that it was being used somwhere in another server, in a simple query. At least you should check if your solution won't cause the above problem. Maybe someone else could elaborate on how optimizer behave when dealing with linked servers?