Referencing another database from T-SQL stored proc without hardcoding the database name

Question

Sample code references table from a different database then the current on.

use DB1
select * from SomeTableInDB1
select * from DB2..SomeTableInDB2

QUESTION : Can the name of the other database ("DB2") be soft-coded somehow?

something like this ( I know this will not work but maybe it makes my question clearer)

use DB1
varchar @OtherDB
set @OtherDB = "DB2"
select * from SomeTableInDB1
select * from @OtherDB..SomeTableInDB2

Answer 1

You could also use a synonym, which I find is a little cleaner than a view (the view shouldn't have SELECT * but will also need to be maintained as the underlying table changes).

USE DB1;
GO
CREATE SYNONYM dbo.SomeTableInDB2 FOR DB2.dbo.SomeTableInDB2;

This is effectively just a redirect - indexes from the base table are still used the same way etc. So now in DB1 you can say:

SELECT * FROM dbo.SomeTableInDB2;

...without hard-coding the database.

For more information see CREATE SYNONYM (MSDN).