Abstracting a Foreign Database reference

Question

I want to query database two from database 1. Easy, right?

SELECT * FROM database2.dbo.TableName

Sure. But what if the actual name of database2 changes? I have to go back and change all of my procs. Imagine if the instance of database2 on the staging server is named "database2_staging"...

What I'd like is an abstraction that I could point to, and then only have to change the abstraction. Something like:

SELECT * FROM GetSecondaryDatabaseReference().TableName

I've looked at linked servers, but this solution would be intra-server. Maybe I could link the same server to itself? I dunno.

Thoughts?

TIA.

Answer 1

You could use CREATE SYNONYM to make synonyms for each remote table, and reference those in all the queries.

Answer 2

Use a view.

CREATE VIEW remotetable AS SELECT ... FROM db2.dbo.table

The view can point to a table in the same database, a table in a different database in the same instance, or to a table anywhere else using a linked server.