MSSQL avoid multiple prefixes by using alias

Question

I am using al lot of sql queries and tyred of typing the complete prefixes of

[LINKED_SERVER_ALIAS].[LINKED_SERVER_ON_LINKED_SERVER].[DATABASEPATH].[SCHMEMA].TABLE

No way to change the serverstructure or direct login to linked server on another linked server.

Question: Is there som transact sql command to create a global alias like create alias my_linked_connection for [LINKED_SERVER_ALIAS].[LINKED_SERVER_ON_LINKED_SERVER].[DATABASEPATH].[SCHMEMA].TABLE

that it is possible to use:

select * from my_linked_connection.TABLE

an additional problem is, that these are to many prefixes, so a normal select query is only possible by openquery or declare @cmd ... exec @cmd

Thanks

Combine a part of the prefixes inside the linked server alias sp_addlinked server.

Answer 1

Synonym is what you are looking for here

CREATE SYNONYM schema.tablename for linkedservername.remotedatabasename.schema.tablename

This has the advantage (which I expect is what you are looking for) that you can move views, functions and procedures through your development environments whithout having to modify the object code; the only thing that should be different is that the target database for the synonym will be different each time.

Note that Synonym is a MSSQL feature and may not be supported by your ODBC/JDBC drivers so please test fully before deployment.