CODEWITHSUNDEEP
sqlsql-server-2005
gaijintendo
gaijintendo

Reputation: 423

How to reference another Database in a generic manner

We have live and demo systems, each using of a pair of databases. One database often reports from the other.

Quite often the demo site has a reference like this

SELECT  Columns
FROM    OtherDatabase_demo.dbo.Tablename

So the live version would say:

...FROM    OtherDatabase.dbo.Tablename

When it comes to publsihing I compare the stored procedures between live and demo (using dbForge Schema Compare in my case) every differing reference is highlighted, and creates a lot of noise.

Is there any way to abstract these references so I can make that distinction in one single location?

Upvotes: 2

Views: 1144

Answers (1)

anon
anon

Reputation:

Yes, use a synonym. In one database:

CREATE SYNONYM dbo.MyTableName FOR OtherDatabase_demo.dbo.Tablename;

And in the live version:

CREATE SYNONYM dbo.MyTableName FOR OtherDatabase.dbo.Tablename;

Now your script can say...

SELECT  Columns
FROM    dbo.MyTableName

...in both databases, allowing your procedures to be identical.

Your diff scripts may pick up the different definitions for the synonyms, but hopefully you can ignore those (either with the tool or just consciously).

We've asked for the ability to alias a database, but they don't understand how useful this could be:

http://connect.microsoft.com/SQLServer/feedback/details/311079/expand-synonym-to-other-entities-database-linked-server

http://connect.microsoft.com/SQLServer/feedback/details/288421/allow-create-synonym-for-database

Upvotes: 9

Related Questions