SQL Server: Using 4 part identifiers when database may be on the same server

Question

In my client's setup, the database servers in lower environments are all on one server for cost saving purposes, but in higher environments, these databases are deployed on separate servers, and linked servers are used when queries run across the servers.

Example setup:

Dev:
 Server1: DB1, DB2
UAT: 
 Server1: DB1 (with linked server for DB2)
 Server2: DB2

I am trying to maintain the databases with a single set of SQL Server Data Tools projects, using 4 part [server].[database].[schema].[object] identifiers and database reference variables to model the relationships. This works well when the databases are not on the same server, but fails when the databases are on the same server.

My questions:

1. Is there a default way to refer to the local server in a 4 part identifier other than omitting the server definition?
2. If not, is there a way to add a linked server pointing to the local server?
3. If neither of the above is possible, is there another way to manage this in SQL Server?

• Aasim Abdullah: You can not add a local server as linked server but still you can use server full name for four part naming i.e. [server].[database].[schema].[table/view]

Answer 1

Use a synonym, and just don't sync the synonyms. So when it is on a local server:

CREATE SYNONYM dbo.FooBar FOR DBName.dbo.TableName;

When it is remote:

CREATE SYNONYM dbo.FooBar FOR ServerName.DBName.dbo.TableName;

Now all your code just has to reference dbo.FooBar no matter where it is.

• Ryan Gross: This seems like a logical solution to the problem
• Cruncher: +1, I didn't know you could do this. I may suggest something like this at work to make our shenanigans simpler.