SQL Server – How to Identify the Object Behind a Database Synonym

Start out with getting all the procedures from sys.procedures

SELECT  * FROM sys.Procedures 

Then use EXEC sp_helptext on each to load the text of the procedures.

Search for text to add, add if needed,

Then load the updated text to a variable and execute dynamically.

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 <database>

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' <database1> to a specific database on that server. The stored procedure would read the configuration table and REPLACE <database1> with the name retrieved from the configuration table.