CODEWITHSUNDEEP
sqlsql-serversql-server-2008
renathy
renathy

Reputation: 5355

change all synonyms to another database

I have production database (for example, PROD1). It has a lot of synonyms to another database (for example, PROD2).

I have created PROD2_TEST (as fully copy of PROD2) and also PROD1_TEST (as full copy of PROD1). But as a result I have PROD1_TEST synonyms to PROD2, instead of PROD2_TEST.

How, can I automatically recreate all synonyms to the test database?

Upvotes: 7

Views: 19938

Answers (5)

Phillip
Phillip

Reputation: 315

Made a change to @kuklei 's answer to fix synonyms whose local name is different from the object they are referencing. Also, allow multiple replaces at once with a temp table (please replace the values in the "insert into #Replacements" section with the replacements you want to make):

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

Upvotes: 0

kuklei
kuklei

Reputation: 1205

Based on @artm suggestion I expanded it just a little bit to include parameters to make it more generic. Here is the code

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`

Upvotes: 14

artm
artm

Reputation: 8584

I've had to do this for the same reasons and this is the query I used. Run the query, copy paste the results to the new server (PROD_Test) and run the produced queries. It handles different schema names and multiple databases in case you're using them:

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

Upvotes: 5

Lukek
Lukek

Reputation: 1036

Here's something I used to remap all synonyms to a new database on the same server (based on Tobsey's suggestion)

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

Upvotes: 1

Tobsey
Tobsey

Reputation: 3400

It is not possible to alter a synonym, you will have to drop and create them. The following article contains a script to create synonyms for a range of tables. Hopefully you can modify it to suit your needs:

Use synonyms to abstract SQL Server objects

You can view all of the existing synonyms using the sys.synonymsview. In pseudocode what you want to do is:

  • Create a temporary table to store the synonym name and base_object_name
  • Select all of the incorrect synonyms from sys.synonyms into the temporary table
  • Drop the incorrect synonyms
  • Construct the statement to replicate the synonym but changing PROD2 to PROD2_TEST
  • Run that statement

Upvotes: 1

Related Questions