How to Create an Alias of Database in SQL Server

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)

Microsoft SQL Server 2005: Create alias for database

You can do replication from one database to another database on the same machine. You can also copy data directly without having to create an alias. For instance if you had a table named Users in DB2 and a Users table in DB1 and they are the same schema you could easily just do

INSERT INTO DB1..Users
select * from DB2..Users

Now, a synonym would allow you to use a table from DB2 as if it was a table in DB1 so for instance if you have a table named Products in DB2 you could do

use DB1
GO

CREATE SYNONYM [dbo].[Products] FOR [DB2].[dbo].[Products]
GO

-- Now the following would give you the same result
select * from DB2..Products
select * from Products

For more information on synonyms see here

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'


Related Topics



Leave a reply



Submit