如何为数据库名创建Sql同义词或“别名”?

I'm using ms sql 2008 and trying to create a database name that references another database. For example 'Dev', 'Test', 'Demo' would be database names that i could reference from my multiple config files, but each name would point to another database such as 'db20080101' or 'db20080114'.

我正在使用ms sql 2008，并尝试创建引用另一个数据库的数据库名称。例如'Dev'、'Test'、'Demo'将是我可以从多个配置文件中引用的数据库名称，但每个名称都指向另一个数据库，如'db20080101'或'db20080114'。

[Edit]Some of the configs are for applications that i control the code and some aren't (ex. MS Reporting service datasource file configs)[/Edit]

[编辑]有些配置是用于我控制代码的应用程序，有些则不是(例如，MS Reporting service datasource file configs)[/Edit]

It seems that sqlserver only supports synonyms for View,Table,Sproc, or Function. And Alias' are for table and column names.

sqlserver似乎只支持视图、表、Sproc或函数的同义词。别名“用于表和列名”。

Is there a way to do this that i missed in the docs? Any one have any suggestions on a workaround?

有什么方法可以做到我在文档中漏掉的吗?有人对解决办法有什么建议吗?

4 个解决方案

#1

use 3 part notation and alias up to the table, example

使用3个部分表示法和表的别名，例如

select * from tempdb.dbo.sysobjects a
join master.dbo.sysobjects b on a.id = b.id

#2

I've done something similar to this using another config file.

我使用另一个配置文件做了类似的事情。

The new config file maps your generic name to all of the information needed to connect to that database (db name, user name, password, etc.) and then your connection function takes your generic name as an argument.

新的配置文件将通用名称映射到连接到该数据库所需的所有信息(db名称、用户名称、密码等)，然后连接函数将通用名称作为参数。

db.config:

DEV_DB_NAME = db20080101
DEV_DB_USER = dev_user
DEV_DB_PASS = dev_pass
TEST_DB_NAME = db20070101
TEST_DB_USER = test_user
TEST_DB_PASS = test_pass

connection code:

连接代码:

db_connection get_connection(string prefix) {
    db_connection db_conn = new db_connection;
    string db_name = get_config_value(config_path, prefix + "_DB_NAME");
    string db_user = get_config_value(config_path, prefix + "_DB_USER");
    string db_pass = get_config_value(config_path, prefix + "_DB_PASS");

    db_conn.connect(db_name, db_user, db_pass);

    return db_conn;
}

Then you just call get_connection() with your db alias as the argument.

然后用db别名调用get_connection()作为参数。

#3

There is a way to simulate this using a linked server. This assumes you have two SQL servers with the same set of databases one for development/test and one live.

有一种方法可以使用链接的服务器来模拟它。这假定您有两个SQL服务器，它们具有相同的数据库集，一个用于开发/测试，另一个用于活动。

Open SQL Server Management Studio on your development/test server
在您的开发/测试服务器上打开SQL Server Management Studio
Right click Server Objects > Linked Servers
右键单击服务器对象>链接服务器
Select New Linked Server...
选择新的连接服务器…
Select the General page
选择General页面
Specify alias name in Linked server field - this would normally be the name of your live server
在链接服务器字段中指定别名——这通常是您的活动服务器的名称
Select SQL Native Client as the provider
选择SQL Native Client作为提供程序
Enter sql_server for Product Name
输入sql_server作为产品名称
In Data Source specify the name of the development server
在数据源中指定开发服务器的名称
Add Security and Server Options to taste
添加安全性和服务器选项
Click OK
单击OK

The above is for SQL Server 2005 but should be similar for 2008

以上是针对SQL Server 2005的，但对于2008应该是类似的

Once you've done that you can write SQL like this:

一旦你这样做了，你就可以这样写SQL:

SELECT * FROM liveservername.databasename.dbo.tablename

Now when your scripts are run on the development server with the linked server back to itself they will work correctly pulling data from the development server and when the exact same scripts are run on the live server they will work normally.

现在，当您的脚本在开发服务器上运行时，将链接服务器回自己，它们将正确地从开发服务器提取数据，当在活动服务器上运行相同的脚本时，它们将正常工作。

#4

I know this probably will not help in all situations, but you still have the option of using views. You can insert, delete, update, select into a view as long as it has a proper identity key (Primary Key). If you point it to another database, you should drop and recreate to get the different schema (in case you're working between production and test while making changes to the schema in test and/or production.

我知道这可能不会在所有情况下都有帮助，但是您仍然可以选择使用视图。只要视图具有合适的标识键(主键)，就可以插入、删除、更新、选择。如果您将它指向另一个数据库，您应该删除并重新创建以获得不同的模式(如果您在生产和测试之间工作，同时在测试和/或生产中对模式进行更改的话)。

Synonyms are useful for when you're going to another database and have a 3 or 4 part name, but when you want to make it so you can have a set name, a linked server will also work which will let you use a fixed name if the table names are the same in both databases and you're just pointing between prod and test.

同义词是很有用的,当你去到另一个数据库和一个3或4部分的名字,但是当你想要它,这样你就可以有一组名称、联系服务器也将会让你用一个固定的名字如果工作表名是一样的在这两个数据库和你只是指向之间的刺激和测试。

#1