Synonym/Alias equivalent for a Database If we have code in an SP Database1 then queries Database2 (on the same server) we want the same code to work on the databases Database1Dev and Database2Dev. But this currently means editing the full SP each time we push to Live.

We want a single of line of code such as

select col1, col2 from databaseName..ourTable


Where databaseName is somehow set at a server level.

I understand synonyms are the wrong solution for this. Is there something similar we can use that doesn't include writing a synonym for every single object?
You can create a synonym to the object, however you cannot currently create a synonym to a database. This is a popular request on Connect:


Expand synonym to other entities (database, linked server)
Allow CREATE SYNONYM for &lt;database&gt;


So, as you've stated in your question, you'd need to run CREATE SYNONYM for each object affected. Like this, in the dev instance:

CREATE SYNONYM MyObjectSyn
FOR Database1Dev.dbo.MyObject;


And like this in the prod instance:

CREATE SYNONYM MyObjectSyn
FOR Database1.dbo.MyObject;


Then the stored procedure code references MyObjectSyn instead.

Another way to get around this problem, that I don't recommend, is to use a linked server connected to each database.  The linked server can be repointed on dev to Database1Dev, and Database1 on prod.

Or, as Scott Hodgin suggested: use dynamic SQL and a configuration table on each server that would 'map' &lt;database1&gt; to a specific database on that server.  The stored procedure would read the configuration table and REPLACE &lt;database1&gt; with the name retrieved from the configuration table.