如何在 SQL Server 中创建数据库的别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21722066/
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 an alias of database in SQL Server
提问by Dmitriy Sosunov
We have a very old software has been created around 10 years ago and we don't have source code.
我们有一个大约 10 年前创建的非常古老的软件，我们没有源代码。
The software uses two databases, DB01and DB02on the same SQL Server 2012 instance.
该软件使用两个数据库，DB01并DB02在同一个 SQL Server 2012 实例上。
There is SQL statements such as db01..table1 join db02..table2, but the main issue is our processes don't allow us use db02as a name of database.
有 SQL 语句，例如db01..table1 join db02..table2，但主要问题是我们的进程不允许我们db02用作数据库名称。
The question is: how we can create an alias of for database?
问题是：我们如何为数据库创建别名？
I was trying to use CREATE SYNONYM
我试图使用 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.
请建议如何在不修补二进制文件以更正 SQL 语句的情况下解决它。
采纳答案by Charles
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..
使用您要模拟的名称创建一个数据库。重新调整 DDL 代码生成器，为数据库中的每个表创建一个视图，这些表具有我需要通过硬编码名称访问的表。基本上，每个视图都会有一个看起来像这样的语句..
CREATE VIEW schemaname.tablename as SELECT * FROM targetdbname.schemaname.tablename
Example:
例子：
The target database name that is hardcoded is called ProdDBV1and the Source DB you have is named ProductDatabaseDatabaseV1, schema is dboand table name is customer
硬编码的目标数据库名称被调用ProdDBV1，您拥有的源数据库被命名ProductDatabaseDatabaseV1，架构是dbo，表名称是customer
- Create the database called
ProdDBV1using SSMS or script. CREATE VIEW dbo.customer as SELECT * FROM ProductDatabaseDatabaseV1.dbo.customer
- 创建
ProdDBV1使用 SSMS 或脚本调用的数据库。 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_msforeachtableprocedure if possible)
如果您可以枚举“源”数据库中的每个表，然后如上所述创建 DDL。如果您愿意，我可以使用代码示例更新此帖子。（sp_msforeachtable如果可能，使用该程序）
回答by maxcastaneda
I had a similar issue.
Solved with this workaround, using synonyms.
我有一个类似的问题。使用同义词
解决了此解决方法。
Short version:You flood your database with a synonym of every object you'll ever need to reference. Later you re-create every synonym with the other database name.
简短版本：你用你需要引用的每个对象的同义词淹没你的数据库。稍后您用另一个数据库名称重新创建每个同义词。
回答by Adam Hardy
Here's a stored proc to do it. Simply add it to your database and call it with the target database. It will create synonyms for all tables in the target database, and create the schemas if they don't exist. I've left a commented out section in case someone knows of a way to get the create schemas working without a cursor.
这是一个存储过程来做到这一点。只需将其添加到您的数据库并使用目标数据库调用它。它将为目标数据库中的所有表创建同义词，如果模式不存在，则创建模式。我留下了一个注释掉的部分，以防有人知道一种让创建模式在没有光标的情况下工作的方法。
CREATE PROCEDURE CreateSynonymsForTargetDatabase (
@databaseName sysname
)
AS BEGIN
DECLARE @TSQL nvarchar(max) = N''
DECLARE @rn char(2),
@SchemaName sysname;
SET @rn = char(13) + char(10)
CREATE TABLE #DBSynonym(
[Schema] sysname NOT NULL,
[Table] sysname NOT NULL
)
SET @TSQL = N'
INSERT INTO #DBSynonym ([Schema], [Table])
SELECT Schemas.name, Tables.name
FROM [' + @databaseName + '].sys.tables
INNER JOIN [' + @databaseName + '].sys.schemas on tables.schema_id = schemas.schema_id
'
EXEC (@TSQL)
SET @TSQL = N''
DECLARE MissingSchemasCursor CURSOR
READ_ONLY
FOR
SELECT newSchemas.[Schema]
FROM #DBSynonym newSchemas
LEFT JOIN sys.schemas on newSchemas.[Schema] = schemas.name
WHERE schemas.schema_id is null
GROUP BY newSchemas.[Schema]
OPEN MissingSchemasCursor
FETCH NEXT FROM MissingSchemasCursor INTO @SchemaName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @TSQL = N'CREATE SCHEMA ' + QUOTENAME(@SchemaName) + N';'
EXEC sp_executesql @TSQL
END
FETCH NEXT FROM MissingSchemasCursor INTO @SchemaName
END
CLOSE MissingSchemasCursor
DEALLOCATE MissingSchemasCursor
/*
SELECT @TSQL = @TSQL +
N'
GO
CREATE SCHEMA ' + QUOTENAME([Schema]) + N';'
FROM #DBSynonym newSchemas
LEFT JOIN sys.schemas on newSchemas.[Schema] = schemas.name
WHERE schemas.schema_id is null
GROUP BY newSchemas.[Schema]
PRINT 'CREATE SCHEMAS : ' + ISNULL(@TSQL,'')
EXEC sp_executesql @TSQL
*/
SET @TSQL = N''
SELECT @TSQL = @TSQL +
N'
CREATE SYNONYM ' + QUOTENAME([Schema]) + N'.' + QUOTENAME([Table]) + N'
FOR ' + QUOTENAME(@databaseName) + N'.' + QUOTENAME([Schema]) + N'.' + QUOTENAME([Table]) + N';'
FROM #DBSynonym
EXEC sp_executesql @TSQL
SET @TSQL = N''
END
GO
Use it as follows :
使用方法如下：
EXEC CreateSynonymsForTargetDatabase 'targetDbName'
回答by Suzie
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.
Open Cabinets table and go right at the bottom and create the name of the Alias cabinet you want.
转到您要创建别名的数据库，
使用首选设计创建别名文件夹表，
转到唯一 ID 的表并检查创建的表的最后一个代码序列。
例如，如果最后一个代码是 10，则将其更新为 11。
打开橱柜表并在底部右侧创建您想要的别名橱柜的名称。
回答by Jeff Moden
The question is: how we can create an alias of for database?
问题是：我们如何为数据库创建别名？
I know this is an old post but...
我知道这是一个旧帖子，但是......
This is why I only use the 2 part naming convention for SQL objects. It allows me to have 2 part synonyms that point to differently named databases depending on what environment I'm in. There are some places where it doesn't work so well but, for the most part, those places are very rare.
这就是为什么我只对 SQL 对象使用 2 部分命名约定。它允许我有两部分同义词，根据我所处的环境指向不同命名的数据库。有些地方它不能很好地工作，但在大多数情况下，这些地方非常罕见。
As for software that you don't have the source code of and if that software uses the 3 part naming convention, you're probably just out of luck unless you know what the 3 part naming convention is for each object and create a 3 part synonym for each object.
至于您没有源代码的软件，如果该软件使用 3 部分命名约定，您可能只是运气不好，除非您知道每个对象的 3 部分命名约定是什么并创建一个 3 部分命名约定每个对象的同义词。
回答by Magier
I found Charles' answer (and the linked workaround in the comment by maxcastaneda) very useful. I followed this approach and it works for me. I have streamlined it a bit and created the following query that brings up all required synonyms to create.
我发现查尔斯的回答（以及 maxcastaneda 评论中的链接解决方法）非常有用。我遵循了这种方法，它对我有用。我对其进行了一些简化，并创建了以下查询，以显示所有需要创建的同义词。
As a prerequisite for this snippet both the original DB and the synonym/alias db have to be on the same server otherwise in case you use linked server or so you have to modify it a bit. It should be fairly easy to put this into a small sp to update the synonyms automatically.
作为这个片段的先决条件，原始数据库和同义词/别名数据库必须在同一台服务器上，否则，如果您使用链接服务器，或者您必须稍微修改它。将其放入一个小 sp 中以自动更新同义词应该相当容易。
USE <SYNONYMDB>
SELECT
'[' + TABLE_NAME + ']',
'[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']',
'IF EXISTS (SELECT * FROM sys.synonyms WHERE name = ''' + TABLE_NAME + ''') DROP SYNONYM ['+ TABLE_NAME + ']; CREATE SYNONYM [' + TABLE_NAME + '] FOR <ORIGINALDB>.' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']' AS SynonymUpdateScript FROM <ORIGINALDB>.INFORMATION_SCHEMA.TABLES
Don't forget to enter you Db names at the <...> spots.
不要忘记在 <...> 位置输入您的数据库名称。
Just copy the content of the SynonymUpdateScriptColumn and execute it in the synonym DB - or create a stored procedure for this task.
只需复制SynonymUpdateScriptColumn的内容并在同义词数据库中执行它 - 或者为此任务创建一个存储过程。
Be aware there is an issue if you have views in place that refer to tables or other db objects without the 2 part naming convention. Those synonyms won't work. You should fix this in the original objects / views.
请注意，如果您有引用表或其他 db 对象而没有 2 部分命名约定的视图，则会出现问题。这些同义词不起作用。您应该在原始对象/视图中修复此问题。
回答by BAdmin
You can create an alias from 'SQL Server Configuration Manager' under Configuartion Tool in SQL Server Folder.
您可以从 SQL Server 文件夹中配置工具下的“SQL Server 配置管理器”创建别名。
Detailed source : http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/
详细来源：http: //www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/
