SQL Server交叉数据库别名 [英] SQL Server cross database alias

问题描述

我想了解如何使用别名来引用同一个实例中的其他数据库，而不必使用硬编码的名称。

如下：

我有一个数据db与stores数据，一个审计数据库保留所有更改。由于各种原因，我想将审计数据保存在单独的数据库中，不仅仅因为它可能会非常大，并用于报告目的。

不希望通过硬编码名称引用此名称，但是使用别名，以便在不同环境中，我不必更改名称和各种sp以引用新名称。

例如：

  mydevdata 
 mydevaudit 
   mydevdata 中存在sp，如调用 mydevaudit  $ c>，我不想改变sp当我去测试，其中db可以调用 mytestdata 和 mytestaudit 。再次，由于各种原因，数据库名称可以更改，更多地与空格的实例等。
 
 
 
因此，如果我在 mydevdata ：
  proc A 
 begin 
 
插入mydevaudit。 table.abc（somecol）
 select 1 
 
 end 
  
当我去测试，我不想改变程序来引用另一个名称，（假设为发生的争论）
 
 
 
相反，我寻找做类似的操作：
  proc A 
 begin 
 
插入到AUDITEBALIAS.table.abc （somecol）
 select 1 
 
 end 
  
 
 
 
 
此外，dymnamic SQL不是一个选项。
 
 $ 
解决方案
您可以使用同义词 
  CREATE SYNONYM WholeTableAliasWithDBetc FOR TheDB.dbo.TheTable 
  
这意味着本地
 
 
 
您还可以使用审核DB中的存储过程  。有一种第三种形式的 EXEC ，很少用于您可以参数化存储过程名称
  DECLARE @module_name_var varchar（100）
 SET @module_name_var ='mydevaudit.dbo.AuditProc' 
  -  SET @module_name_var ='whatever.dbo.AuditProc'
 EXEC @module_name_var @ p1，@ p2，... 
  
很明显，你可以改变module_name_var来使用你喜欢的数据库。
 
I'm trying to understand how I can use an alias to reference another database in the same instance, without having to use a hardcoded name.

The scenario is as below:

I have a data db with stores data, an audit db which keeps all changes made. for various reason, i want to keep the audit data in a separate database, not least because it can get quite large and for reporting purposes.

In the data db, I don't want to reference this by a hardcoded name but an alias so that in different environments, I don't have to change the name and various sp's to reference the new name.

for example:
mydevdata
mydevaudit
If a sp exists in mydevdata such as which calls the mydevaudit, I don't want to change the sp when I go to test where the db's may be called mytestdata and mytestaudit. Again, for various reasons, the database names can change, more to do with spaces an instances etc. 

So if I had procedure in mydevdata:
proc A
begin

insert into mydevaudit.table.abc(somecol)
select 1

end
when I go to test, I don't want to be change the procedure to reference another name, (assume for sake of argument that happened)

Instead I am looking to do something like:
proc A
begin

insert into AUDITEBALIAS.table.abc(somecol)
select 1

end
I am interested in finding out how I could do something like that, and the pro's and cons.

Also, dymnamic SQL is not an option.

thanks in advance for you help.
解决方案
You may be able to use synonyms
CREATE SYNONYM WholeTableAliasWithDBetc FOR TheDB.dbo.TheTable
This means all object references in the local DB are local to that DB, except for synonyms that hide the other database from you.

You can also use stored procedures in the audit DB. There is a 3rd form of EXEC that is little used where you can parametrise the stored proc name 
DECLARE @module_name_var varchar(100)
SET @module_name_var = 'mydevaudit.dbo.AuditProc'
--   SET @module_name_var = 'whatever.dbo.AuditProc'
EXEC @module_name_var @p1, @p2, ...
Obviously you can change module_name_var to use whatever DB you like

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