Synonyms

We have a number of stored procs/functions in a support databse which
looks to a production database for the data. Rather than do this in
each procedure:

SELECT col1 FROM ProdServer.dbo.MyTable

We were hoping to create a synonym, say SourceDB, for ProdServer.dbo
and prepend it to the table name like this:

SELECT col1 FROM SourceDB.MyTable

Then we can easily change the data source simply by changing the
synonym.

Unfortunately this doesn't work. I've only been able to create a
synonym that points to a specific databse object but that means I need
to create and maintain one synonym for each object. Is there any way
to do what I'm asking?

Thanks

Carl

Synonyms point to specific database objects. They do not apply to the
server in general. But your idea of changing the synonym is sound and
is a way to make your code dynamic without doing dynamic SQL.

The table and column information is stored in the
Inforation_schema.tables and information_schema.columns views. You
could create synonyms to say each information_schema.columns view in
each of your databases, then select * from the columns views in each
database to determine which tables have col1 and create a synonym on
the fly that points to that table in that database and execute the
other procedure that utilizes the synonym.

-Eric Isaacs