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,

Depends on the database product you're using (SQL is only the query language - not a product....) — marc_s
– marc_s, Commented Mar 22, 2013 at 15:17

Gordon Linoff · Accepted Answer · 2013-03-22 15:22:38Z

7

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;

answered Mar 22, 2013 at 15:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AdamL · Accepted Answer · 2013-03-22 15:29:33Z

4

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?

answered Mar 22, 2013 at 15:29

AdamL

13.3k5 gold badges57 silver badges79 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related