如何为数据库名称创建 Sql 同义词或“别名”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/444139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create Sql Synonym or "Alias" for Database Name?
提问by Kevin McKinley
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 报告服务数据源文件配置）[/编辑]
It seems that sqlserver only supports synonyms for View,Table,Sproc, or Function. And Alias' are for table and column names.
似乎sqlserver 只支持View、Table、Sproc 或Function 的同义词。别名用于表名和列名。
Is there a way to do this that i missed in the docs? Any one have any suggestions on a workaround?
有没有办法做到这一点，我在文档中错过了？有人对解决方法有任何建议吗？
回答by SQLMenace
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
回答by Ryan Ahearn
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.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.
然后您只需使用您的数据库别名作为参数调用 get_connection() 。
回答by Ryan Ahearn
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
- Right click Server Objects > Linked Servers
- Select New Linked Server...
- Select the General page
- Specify aliasname in Linked server field - this would normally be the name of your liveserver
- Select SQL Native Client as the provider
- Enter sql_serverfor Product Name
- In Data Source specify the name of the developmentserver
- Add Security and Server Options to taste
- Click OK
- 在开发/测试服务器上打开 SQL Server Management Studio
- 右键单击服务器对象 > 链接服务器
- 选择新建链接服务器...
- 选择常规页面
- 在链接服务器字段中指定别名- 这通常是您的实时服务器的名称
- 选择 SQL Native Client 作为提供程序
- 输入sql_server作为产品名称
- 在数据源中指定开发服务器的名称
- 添加安全和服务器选项来品尝
- 单击确定
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.
现在，当您的脚本在开发服务器上运行且链接服务器返回自身时，它们将正常工作，从开发服务器中提取数据，并且当完全相同的脚本在实时服务器上运行时，它们将正常工作。
回答by Lauren Glenn
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 部分名称时，同义词很有用，但是当您想使用它来设置名称时，链接服务器也可以使用，它可以让您使用固定名称如果两个数据库中的表名相同，并且您只是指向 prod 和 test 之间。
