How to Create SQL Synonym or "Alias" for Database Name

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)

Can I alias a database name or create a synonym for a database?

Looks like this is not currently a possibility with SQL Server

Alias/Synonym for a database based on user

Is it possible to have the same query, but based on different user currently logged in, request the info from different databases?

Sure. Logins have a default database and users have a default schema.

Both of these can affect object name resolution.

if you use a 1-part name, like

SELECT * FROM clients

then SQL Server will look for an object named "clients" in the user's default schema first, then in the dbo schema in the current database.

If you use a 2-part name, like

SELECT * FROM foo.clients

then SQL Server will look for an object named "clients" in in the "foo" schema in the current database.

---

Related Topics