sqlsql-serverdatabasealiassynonym

How to create an alias of database in SQL Server

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.

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

    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)