How to create an alias of database in SQL Server
alias, database, sql, sql-server, synonym
Solution
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`
- Create the database called `ProdDBV1` using SSMS or script.
- `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)
Problem
We have a very old software has been created around 10 years ago and we don't have source code. The software uses two databases, `DB01` and `DB02` on the same SQL Server 2012 instance. There is SQL statements such as `db01..table1 join db02..table2`, but the main issue is our processes don't allow us use `db02` as a name of database. The question is: how we can create an alias of for database? I was trying to use `CREATE SYNONYM` ``` CREATE SYNONYM [db02] FOR [db02_new_name]; ``` but it doesn't work for database names. Please suggest how it can be solved without patching a binary files to correct SQL statements.