Como gerenciar melhor as referências de banco de dados cruzados codificadas em procs armazenados?

Referenciando o último último parágrafo da sua pergunta

Eu tenho que acreditar que este é provavelmente um problema comum que foi resolvido, mas estou me perguntando se a solução é algo que ainda posso fazer em cima da implementação que temos ou se é algo que precisava ser projetado de forma diferente para começar. e em caso afirmativo, qual é o design "adequado" para lidar com esse problema?

Sim, tenho visto muitos posts e blogs sobre este assunto e a abordagem recomendada que tenho visto é usar SYNONYMS .

Um sinônimo é como um alias. Ele fornece uma maneira de fazer referência a um objeto como uma tabela, visualização ou procedimento armazenado, mas a parte desafiadora é que a SYNONYMsó pode ser definida como um nome de duas partes (schema.object). Essa limitação exigiria que você fizesse alterações de código em seus objetos existentes que fazem referência a nomes de 3 partes (Database.Schema.Object).

Aqui está um exemplo de um sinônimo da documentação. O exemplo a seguir cria primeiro um sinônimo para o objeto base, Product no banco de dados AdventureWorks2012 e, em seguida, consulta o sinônimo.

-- Create a synonym for the Product table in AdventureWorks2012.  
CREATE SYNONYM MyProduct  
FOR AdventureWorks2012.Production.Product;  
GO  

-- Query the Product table by using the synonym.  
SELECT ProductID, Name   
FROM MyProduct  
WHERE ProductID < 5;  
GO  

--You can also create a `SYNONYM` for a remote object.
CREATE SYNONYM MyEmployee FOR Server_Remote.AdventureWorks2012.HumanResources.Employee;  

Em sua situação particular, vamos supor que você tenha referências a db1.dbo.Customers. Você pode criar um sinônimo chamado Db1Dbo.Customersque aponta para db1.dbo.Customers. Quaisquer objetos que atualmente fazem referência db1.dbo.Customersprecisariam ser alterados para Db1Dbo.Customers.

Agora, você deseja fazer uma cópia do banco de dados db1e chamá-lo de db1copy. Depois de criar o db1copybanco de dados, basta descartar o existente synonymno banco db1copyde dados DB1Dbo.Customerse recriá-lo para apontar para db1copy.dbo.Customers.

Existem algumas 'pegadinhas' ao usar SYNONYMSembora. Dê uma olhada no post de Aaron Bertrand - #BackToBasics: The Beauty of the Synonym . Como ele mesmo diz: ' Mas nem tudo são rosas '. Entre as 'pegadinhas' que Aaron discute estão:

• Eles introduzem uma camada de abstração.
• O IntelliSense nem sempre funcionará em determinadas versões.
• Você não pode ignorar o desempenho.
• Você não pode alterar um sinônimo.
• Você só pode apontar para determinados tipos de entidade.
• As permissões podem ficar confusas.