Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm using views and stored procedures that reference a linked server:

FROM [LinkedServer].Database.dbo.FooView

I would like to hide the [LinkedServer].Database or at least the [LinkedServer] part of my views and stored procedures. This way I would be able to have "server-agnostic" code, that would be easily compared between production and dev servers.

This could be done at the server level (like the linked server) or at the database level (where the views and the stored procedures using it are).

share|improve this question
up vote 8 down vote accepted
CREATE SYNONYM dbo.FooView FOR [LinkedServer].Database.dbo.FooView

Then just use dbo.FooView in your SPs and they can be the same between production and dev.

Note that this is only for SQL 2005 and up.

A solution for SQL 2000 might be to use views that ONLY SELECT * FROM [LinkedServer].Database.dbo.FooView with no other code in them. You could have a meta-table with the list of these view names and corresponding named sources. Then, write an SP that loops through the table and (re)creates the views as needed. Then, if you want to change where the views point, you run your SP with the new linked server name as a parameter (or keep it in the meta-table and update that) and all your dependent objects will silently point to the new location now. As long as the column names, positions, and data types are the same, it will work just fine.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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