Change all server name synonyms

• Fellerson

  SSC Veteran

  Points: 205

  More actions

  August 10, 2017 at 8:20 am

  #327927

  Hello

  I work on SQL server migration.

  My DB are now on a server "CONTOSO" and in my DB i have more Synonyms with Server name to "CONTOSO"

  I backuped my DB from my old server CONTOSO and i restore on a new server, "TOTO\UNISQLP1"

  But, i need a script to modify all synonyms parameter Server name "CONTOSO" to "TOTO\UNISQLP1"

  It's possible ? i have more synonyms

  Thank you sorry for my english

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 10, 2017 at 8:47 am

  #1954827

  sure.
  this scripts out the synonyms exactly as is, so I simply added a replace on the definition to change the whichever synonyms had a base_object_name that contains 'CONTOSO' to have the new value.

  make sure you do not have any synonym that referencean object containing CONTOSO, though.

  
DECLARE @vbCrLf varchar(2);
SET @vbCrLf = CHAR(13) + CHAR(10);
SELECT SCHEMA_NAME(schema_id) AS SchemaName, NAME AS SynonymName,

  'IF EXISTS(SELECT * FROM sys.synonyms WHERE SCHEMA_NAME(schema_id) = ''' + SCHEMA_NAME(schema_id) + ''' AND name = '''
        + name
        + ''''
        + ' AND base_object_name <> ''' + base_object_name + ''')'
        + @vbCrLf
        + ' DROP SYNONYM ' + quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name) + ''
        + @vbCrLf
        +'GO'
        + @vbCrLf
        +'IF NOT EXISTS(SELECT * FROM sys.synonyms WHERE name = '''
        + name
        + ''')'
        + @vbCrLf
        --+ 'CREATE SYNONYM ' + quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name) + ' FOR ' + base_object_name +';'
        + 'CREATE SYNONYM ' + quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(name) + ' FOR ' + REPLACE(base_object_name,'CONTOSO','TOTO\UNISQLP1') +';'
        from sys.synonyms;


  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Fellerson

  SSC Veteran

  Points: 205

  More actions

  August 11, 2017 at 12:24 am

  #1954914

  Hello Lowell

  Thank you very much for your reply !

  It's work ! Great

  Thank you