Q:

How to reference another Database in a generic manner

We have live and demo systems, each using of a pair of databases.
One database often reports from the other.
Quite often the demo site has a reference like this
SELECT  Columns
FROM    OtherDatabase_demo.dbo.Tablename

So the live version would say:
...FROM    OtherDatabase.dbo.Tablename

When it comes to publsihing I compare the stored procedures between live and demo (using dbForge Schema Compare in my case) every differing reference is highlighted, and creates a lot of noise.
Is there any way to abstract these references so I can make that distinction in one single location?

A:

Yes, use a synonym. In one database:
CREATE SYNONYM dbo.MyTableName FOR OtherDatabase_demo.dbo.Tablename;

And in the live version:
CREATE SYNONYM dbo.MyTableName FOR OtherDatabase.dbo.Tablename;

Now your script can say...
SELECT  Columns
FROM    dbo.MyTableName

...in both databases, allowing your procedures to be identical.
Your diff scripts may pick up the different definitions for the synonyms, but hopefully you can ignore those (either with the tool or just consciously).
We've asked for the ability to alias a database, but they don't understand how useful this could be:
http://connect.microsoft.com/SQLServer/feedback/details/311079/expand-synonym-to-other-entities-database-linked-server
http://connect.microsoft.com/SQLServer/feedback/details/288421/allow-create-synonym-for-database

