2

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.

  • Can you please clarify what you mean by "and other information"? No mind-readers here. :-) – Aaron Bertrand Apr 8 '14 at 20:56
  • All the 4 object_piece is all the information I need. The and other information part is completely satisfied by your answer, @AaronBertrand. ;-) – Fabricio Araujo Apr 8 '14 at 21:48
3

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;
  • Never heard of that function!!! Brilliant! +1 and accept. – Fabricio Araujo Apr 8 '14 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.