SQL Server Linked Database Aliases

SQL Server Linked Database Aliases

I think the short answer is no. I don't believe database aliasing is supported yet.

Dynamic SQL could do it. You could inject the database name etc and execute your query.

Create alias for SQL server to use in query

InstanceName and alias are two different things.

For local server use Database.dbo.Table

To connect to another server (or another instance) you should add Linked Server
https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine.

Usually you do not need any alias, but if you really want to do this use example:

EXEC sp_addlinkedserver     
   @server=N'S1_instance1',   
   @srvproduct=N'',  
   @provider=N'SQLNCLI',   
   @datasrc=N'S1\instance1'

How to create an alias of database in SQL Server

Create a database with the name you want to impersonate. Re-jigg the DDL code generator to create a view for every table in the database that has the tables I need to access via the hardcoded name. Basically, each view will have a statement that looks like this..

CREATE VIEW schemaname.tablename as SELECT * FROM targetdbname.schemaname.tablename

Example:

The target database name that is hardcoded is called ProdDBV1 and the Source DB you have is named ProductDatabaseDatabaseV1, schema is dbo and table name is customer

1. Create the database called ProdDBV1 using SSMS or script.
2. CREATE VIEW dbo.customer as SELECT * FROM ProductDatabaseDatabaseV1.dbo.customer

If you can enumerate each table in your "source" database and then create the DDL as above. If you want I can update this posting with a code example. (using the sp_msforeachtable procedure if possible)

Aliasing a linked server?

The View editor in Management studio (and enterprise manager before it) is, shall we say, rather limited, and prone to exploding the number of references to a table/view if there are complex conditions.

It's much to be preferred that you learn to write CREATE/ALTER VIEW statements in query windows (there are options to script VIEWs to a new query window as ALTER, if you're wanting to update an existing view).

Alternatively, you can add the linked server using, say, the "Microsoft OLE DB Provider for SQL Server", "SQL Native Server", or any of a number of other providers, rather than using the "SQL Server" provider, and then you can specify a different name for the linked server. (We do this in my shop so that our test servers refer to their partners using the same names as are used on our production servers)

E.g.:

EXEC master.dbo.sp_addlinkedserver @server = N'ALIAS', @srvproduct=N'ACTUALSERVER', @provider=N'SQLOLEDB', @datasrc=N'ACTUALSERVER'

---

Related Topics