Sql-server – How to make inter-database queries with dynamic SQL Server database name

We have two database servers with SQL Server 2005, with two databases each. The secondary server have two SQL Server instances: one for development and other for reporting. Both instances on secondary server restore backups daily from the primary server, but only the development instance could have data modifications.

• The two databases are from different applications, but one database has triggers that alter data on the other database. Triggers update the second database hardcoding its name, like UPDATE database2..thetable

• One database can fetch data from another via SQL queries from one application or Stored Procedures, hardcoded as SELECT FROM database2..thetable

We are considering joining the two SQL Server instances on secondary server (reporting and development) to simplify administration and memory managing, but I couldn't find a solution to make triggers and stored procedures calls another database dynamically and automatically with another name using the same suffix. Ex:

• Instance: RS

  • DB1 (calls DB2 with DB2..table)
  • DB2

• Instance: DEV

  • DB1 (calls DB2 with DB2..table)
  • DB2

Into:

• Instance: default
  • DB1_RS (calls DB2_RS with DB2_RS..table)
  • DB2_RS
  • DB1_DEV (calls DB2_DEV with DB2_DEV..table)
  • DB2_DEV

What are the possible solutions to this? I can think about some, but they are too convoluted:

• Create dynamically T-SQL to be executed via EXEC(). This makes thing more complex because the triggers have lots of code for UPDATES and INSERTS and DELETES.

• Create separate triggers and stored procedures for each group of database names (one for DB1, other for DB1_DEV, other for DB1_RS). This approach seems to not scale…

• Make the database name dynamic on the query: INSERT INTO @db..table. Unfortunately SQL Server doesn't support this… 🙁

Is there a simpler and effective solution to this?

Thanks!