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.
You can temporarily rename a table or a column by giving it another name. This is known as an SQL alias. It's a temporary change that does not affect the actual table name in the database. A temporary table name can also be called a correlation name.
Go to the Database you wish to create Alias, Create an Alias Folders table with the preferred design, Go to unique IDs's table and check the last code sequence for the table created. For example, if the last code is 10, then update it to 11.
SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.
In the left pane of SQL Server Configuration Manager, if you expand the SQL Native Client Configuration folder, there is a subfolder called Aliases (see Figure 1). If we click on this subfolder, we'll see any aliases that have been defined for the system shown in the right pane.
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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With