SQL Server – Getting Database from Synonym Base Object

sql-server-2008synonyms

Risking asking something idiotic, but not easily got by Google-fu, but how to query the database id (and other information) from the base object of an synonym?

The synonym in question is an remote one (pointing to a table on another DB on the same server). The base object itself is not fixed: is the same table on different DB each execution of procedure.

I would like an non-specific method (example: don't want to rely on parsing the base_object_name column on sys.synonyms), but I'll understand if I'm told that's the only choice.

And since it begs the question: concurrency is not an problem, only one client will call that procedure.

Best Answer

This will work for all three-part names, and all four-part names where the server is the local instance.

SELECT 
  name, base_object_name,
  DB_ID(PARSENAME(base_object_name,3)),
  DB_NAME(DB_ID(PARSENAME(base_object_name,3)))
FROM sys.synonyms;

If you get NULL it's either because the synonym is only 2-part (e.g. it references an object in the current database), or the database listed in the synonym no longer exists (it may have been renamed, dropped, etc).

If the four-part name references a remote server, you won't be able to use DB_ID()/DB_NAME(), you'd have to join to the remote server's master.sys.databases - which would involve first parsing out the linked server being used, then constructing some dynamic SQL. Just mentioning that; it doesn't seem to be an issue for your use case.

If you want to limit to 3-part names only, you can add these predicates:

WHERE PARSENAME(base_object_name,3) IS NOT NULL
AND PARSENAME(base_object_name,4) IS NULL;
Related Question