将所有同义词更改为另一个数据库

Question

我有一个生产数据库（例如，PROD1）。它有许多同义词指向另一个数据库（例如，PROD2）。

我已经创建了PROD2_TEST（作为完整的PROD2副本），还有PROD1_TEST（作为完整的PROD1副本）。但结果是，我有一个PROD1_TEST同义词指向PROD2，而不是PROD2_TEST。

如何自动重新创建所有同义词以指向测试数据库？

- renathy

Answer 1

根据@artm的建议，我稍微扩展了一下，加入了参数，以使其更通用。以下是代码：

DECLARE @newDB VARCHAR(MAX) = 'newDB',  --newDB to point the synonym to
    @linkedSrv VARCHAR(MAX) = null --if the synonym points to a linked server than specify it here like '[LinkedSrv].' (including the dot in the end)

SELECT  'Drop Synonym [' + SCHEMA_NAME(schema_id) + '].[' + PARSENAME(base_object_name, 1) + '];' AS dropSynonym,
        ' CREATE SYNONYM [' + SCHEMA_NAME(schema_id) + '].[' + UPPER(PARSENAME(base_object_name, 1)) + '] FOR '
        + COALESCE(@linkedSrv, '') + '[' + @newDB + '].[' + COALESCE(PARSENAME(base_object_name, 2),
                                                                     SCHEMA_NAME(SCHEMA_ID())) + '].['
        + UPPER(PARSENAME(base_object_name, 1)) + '];' AS createSynonym
FROM    sys.synonyms`

- kuklei

Answer 2

出于同样的原因，我必须这样做，以下是我使用的查询。运行查询，将结果复制粘贴到新服务器（PROD_Test），然后运行生成的查询。它可以处理不同的模式名称和多个数据库（如果您正在使用它们）：

SELECT 'Drop Synonym [' + SCHEMA_NAME(schema_id) + '].[' + PARSENAME(base_object_name,1) + '];CREATE SYNONYM [' + 
    SCHEMA_NAME(schema_id) + '].[' + PARSENAME(base_object_name,1) + '] FOR [' + COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) + '_Test].[' 
    + COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) + '].[' + PARSENAME(base_object_name,1) + '];'
FROM sys.synonyms

- artm

---

我使用参数使其更加通用。我已将其添加如下。感谢@artm的建议。 - kuklei

Answer 3

无法更改同义词，您必须删除并重新创建它们。以下文章包含一个脚本，用于为一系列表创建同义词。希望您能修改它以满足您的需求:

使用同义词抽象SQL Server对象

您可以使用sys.synonyms视图查看所有现有的同义词。在伪代码中，您要做的是:

- 创建一个临时表来存储同义词名称和基本对象名称 - 将所有不正确的同义词从sys.synonyms选择到临时表中 - 删除不正确的同义词 - 构造语句以复制同义词，但将PROD2更改为PROD2_TEST - 运行该语句

- Tobsey

Answer 4

这是我曾经用来将所有同义词重新映射到同一服务器上的新数据库的方法（基于Tobsey的建议）。

SET NOCOUNT ON;

DECLARE @db_name SYSNAME
DECLARE @schema_name SYSNAME
DECLARE @cmd NVARCHAR(256)

SET @schema_name = N'dbo'
SET @db_name = N'[newdb]'

DECLARE @schema_id INT

SELECT @schema_id = schema_id
FROM sys.schemas
WHERE NAME = @schema_name;

DECLARE @table_list TABLE (table_name SYSNAME,dest_table_name sysname,dest_schema_name sysname)
DECLARE @table_name SYSNAME
declare @dest_table_name sysname 
declare @dest_schema_name sysname 
DECLARE @prefix NVARCHAR(50)
DECLARE @synonym SYSNAME
DECLARE @linked_server SYSNAME

SET @linked_server = N'SERVER'


-- create a synonym for these tables (insert each table)
INSERT INTO @table_list (table_name,dest_table_name,dest_schema_name)
SELECT name
,'['+SUBSTRING(name,CHARINDEX('.',name,CHARINDEX('.',name,0)+1)+1,LEN(name)-CHARINDEX('.',name,CHARINDEX('.',name,0)+1))+']'
,SUBSTRING(name,CHARINDEX('.',name,0)+1,CHARINDEX('.',name,CHARINDEX('.',name,0)+1)-CHARINDEX('.',name,0)-1)
FROM sys.synonyms
WHERE base_object_name LIKE '![SERVER!].![olddb!]%' ESCAPE ('!')
ORDER BY name

IF EXISTS (
        SELECT *
        FROM sys.servers
        WHERE NAME = @linked_server
        )
    -- 4 part name
    SET @prefix = @linked_server + N'.' + @db_name 
ELSE
    -- 3 part name
    SET @prefix = @db_name 

SELECT TOP 1 @table_name = table_name,@dest_table_name = dest_table_name,@dest_schema_name=dest_schema_name
FROM @table_list

WHILE @table_name IS NOT NULL
BEGIN
    -- set the schema and name for the synonym
    SET @synonym = @schema_name+ N'.' + '['+@table_name+']'

    -- delete the synonym if it exists
    IF EXISTS (
            SELECT *
            FROM sys.synonyms
            WHERE NAME = @table_name
                AND schema_id = @schema_id
            )
    BEGIN
        SET @cmd = N'drop synonym ' + @synonym
        PRINT @cmd
        --EXEC sp_executesql @cmd
    END

    -- create the synonym
    SET @cmd = N'create synonym ' + @synonym + N' for ' + @prefix + N'.'+ @dest_schema_name + N'.'+ @dest_table_name

    PRINT @cmd
    --EXEC sp_executesql @cmd
    PRINT 'GO'

    DELETE TOP (1)
    FROM @table_list

    SET @table_name = NULL

    SELECT TOP 1 @table_name = table_name,@dest_table_name = dest_table_name,@dest_schema_name=dest_schema_name
    FROM @table_list
END
GO

- Lukek

---

当插入到@table_list时，它在创建临时表时会出现一些问题。 在我的情况下，它变为空。 - kuklei

看起来我在变量方面有点懒惰了...你尝试编辑'![SERVER!].![olddb!]%'以匹配你的环境了吗？ - Lukek

Answer 5

对 @kuklei 的答案进行了更改，以修复本地名称与所引用对象不同的同义词。此外，使用临时表允许一次进行多个替换（请在“insert into #Replacements”部分替换要进行的值）：

create table #Replacements
(
     SearchString varchar(100)
    ,OldPrefix varchar(100)
    ,NewPrefix varchar(100)
)

insert into #Replacements
values
     ('_productiondb%','[productiondb]','[not_productiondb]') -- renamed local db
    ,('_192.168.100.1_._productdb%','[192.168.100.1].[productdb]','[productdb]') -- Moved db from remote to local
    ,('_192.168.100.1_._reportdb%','[192.168.100.1].[reportdb]','[not_reportdb]') -- renamed remote db

SELECT
     dropSynonym =
          'DROP SYNONYM ['
        + SCHEMA_NAME(schema_id)
        + '].['
        + name
        + '];'
    ,createSynonym = 
          ' CREATE SYNONYM ['
        + SCHEMA_NAME(schema_id)
        + '].['
        + name
        + '] FOR '
        + REPLACE(base_object_name,OldPrefix,NewPrefix)
    ,synonymName =
          '['
        + SCHEMA_NAME(schema_id)
        + '].['
        + name
        + ']'
    ,oldSynonymTarget = base_object_name
    ,newPrefix
FROM sys.synonyms s
left join #Replacements r
    on s.base_object_name like r.SearchString
order by
    createSynonym

drop table #Replacements

- Phillip