这是我曾经用来将所有同义词重新映射到同一服务器上的新数据库的方法(基于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'
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
)
SET @prefix = @linked_server + N'.' + @db_name
ELSE
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 @synonym = @schema_name+ N'.' + '['+@table_name+']'
IF EXISTS (
SELECT *
FROM sys.synonyms
WHERE NAME = @table_name
AND schema_id = @schema_id
)
BEGIN
SET @cmd = N'drop synonym ' + @synonym
PRINT @cmd
END
SET @cmd = N'create synonym ' + @synonym + N' for ' + @prefix + N'.'+ @dest_schema_name + N'.'+ @dest_table_name
PRINT @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