Skip to main content
Database Administrators

Return to Question

Became Hot Network Question
edited tags
Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663
Source Link
Cade Roux
Cade Roux
  • 6.7k
  • 1
  • 31
  • 55

Checking cross-database synonym validity

Background:

I have multiple source system products, each in their own database which have very limited dependencies on each other, and which are fairly modular and also kind of optional. They are potentially at different version levels in their own maintenance.

I have a separate, data collection system database which gathers usage information from the other systems, and I would like it to continue to be generally forwardly and backwardly compatible with the other systems. This system is all internal to the database - scheduled jobs, procs etc. No external Windows programs, services etc.

Ideally, the source systems would expose consistent interfaces and manage what they expose themselves in a future-proof way, and I do try to utilize that where possible. But there are always exceptions to what the other product's roadmaps are able to accommodate. I would like newer versions of the data collection system to work whether or not a table (or tables) exists in the source system, and simply note that or skip it or log it as appropriate if it's not appropriate to be used. If that system is upgraded later, I do not want to necessarily have to touch the data collection system. So any procs should not have to be altered or re-created.

We generally use synonyms for each source system so that database renaming is handled in as few places as possible (ideally in the synonyms only).

I have experimented a bit and it seems like you can CREATE SYNONYM for a table/view that doesn't exist yet without a failure. And you can CREATE PROC using those synonyms without failure as well. So it would be possible to have the system installed identically on system with and without one of the source databases and should be able to run un-modified when the database for that module is installed (or removed).

I am currently just looking for a way to do the following two things - to know the synonym points to something, and to check that thing and make sure it meets whatever requirements:

  1. To know reliably if a synonym is valid. When the first column here is NULL, is that sufficient to know it is invalid?

SELECT object_id(base_object_name), * FROM sys.synonyms

  1. If a synonym is valid, is there a way to get the metadata to check it out? The columns, datatypes could be gotten from sys.columns (or INFORMATION_SCHEMA), but that needs to be qualified with a known database name - the answer below selects into a temp table first and then looks at the schema of the temp table. Is there any better alternative?

https://stackoverflow.com/a/27126581/18255