Is it possible in a view to reference a linked server implicitly rather than explicitly?

Question

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

SELECT Id, Name
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).

Answer 1

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.