Without actually connecting to another instance of SQL server, is it possible to browse the database objects like tables & views? The answer is YES. How do I do that? Well, for a working scenario we need at least 2 SQL instances either on same server or on a different servers. If you have already not created the Linked server, click here to see this article and then do the below steps.
1. Connect to the 1st instance of SQL Server in Management Studio.
2. Browse through the folders to see “Server Objects” and look for “Linked Servers”. Click the plus sign on it to expand.
3. Now click on the plus (+) sign that is already configured and choose the name of the linked server and then expand Catalogs.
4. Here you can see the number of databases and expand it.
5. You will only see Tables & Views and not the stored procedures or any other objects.
6. You can right click on any table and select “Script Table as” and click on “Select to” => New Query window.
7. You can see a query automatically created pointing to the table in a 4 point convention (…).
Note: Writing any linked server queries for production usage will obviously have performance problems. If the SLA is agreeable then you can proceed or you need to take extra measure. If you have any issue in tackling these type of issues, do get in touch with firstname.lastname@example.org for further information.