Script to list synonym contents

Synonyms are useful in repointing code to point to a different location without changing the code. Some shops use synonyms to deploy the same T-SQL source code to different servers (i.e., development, test, production). I was asked to provide a simple report of what all of the synonyms are actually pointing to. Seems simple, right?

select name, base_object_name
from sys.synonyms
order by name

The problem is that the results aren't convenient for people to read if different databases and schemas are used. SQL Server doesn't have a splitter function, so some type of string manipulation is called for. There are good T-SQL splitter functions that can be found with a search engine, but it's more convenient for people to have a complete, standalone solution with no dependencies.

Writing custom splitting logic takes care. You don't want a false positive messing up the splitting logic. In deference to test driven development, here are my test cases:

create table [test.me] (a int);
create synonym testme for [test.me];
create synonym schemaTestme for dbo.[test.me];
create synonym dbSchemaTestme for sqlblogdb.dbo.[test.me];
create synonym svrDbSchemaTestme for SQLBLOGSVR.sqlblogdb.dbo.[test.me];

After I posted custom splitter logic that worked, Denis Gobo pointed out that the PARSENAME function eliminates the need for any custom splitting logic and it keeps the code simple. Dennis, thanks for the tip. Other readers wanted four part synonyms to be covered, so I modified the code and test cases accordingly. The COALESCE function is necessary to prevent NULL from appearing in the output when the synonym has less than four parts. For example, without COALESCE, the testme synonym would have NULL for serverName, dbName, and schemaName.