如何在SQL Server中创建数据库的别名 [英] How to create an alias of database in SQL Server

问题描述

我们有一个非常古老的软件大约在10年前创建，我们没有源代码。

该软件使用两个数据库， DB01 和 DB02 在同一SQL Server 2012实例上。

SQL语句如 db01..table1 join db02..table2 ，但主要的问题是我们的进程不允许使用 db02 作为数据库的名称。

问题是：我们如何创建数据库的别名？

我试图使用 CREATE SYNONYM

  CREATE SYNONYM [db02] FOR [db02_new_name]; 
  

但不适用于数据库名称。

$ b $

解决方案

建议如何在不修补二进制文件的情况下解决这个问题。您想要模拟的名称。重新启动DDL代码生成器为数据库中的每个表创建一个视图，该表具有需要通过硬编码名称访问的表。基本上，每个视图将有一个如下所示的语句。

  CREATE VIEW schemaname.tablename为SELECT * FROM targetdbname.schemaname .tablename 
  

示例：

目标硬编码的数据库名称称为 ProdDBV1 ，而您拥有的源数据库名为 ProductDatabaseDatabaseV1 ，模式为 dbo 且表名为 customer

1. 使用SSMS或脚本创建名为 ProdDBV1 的数据库。


2. CREATE VIEW dbo.customer as SELECT * FROM ProductDatabaseDatabaseV1 .dbo.customer

如果您可以枚举源数据库中的每个表， DDL如上。如果你想我可以更新此发布与代码示例。 （如果可能，使用 sp_msforeachtable 过程）

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.

解决方案

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)

这篇关于如何在SQL Server中创建数据库的别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！