Had a quick job the other day restoring a pair of databases under different names which is all straight forward enough, but each database in the pair uses quite a few synonyms to access objects in the other database. So with the pair of databases restored under new names, a quick script was needed to drop and recreate all the synonyms so that they point to the correct database name. This is easily achieved with a cursor and a little bit of dynamic SQL.

DECLARE @ObjectName sysname, @Definition VARCHAR(MAX), @Schema VARCHAR(50)
DECLARE @SQL VARCHAR(MAX)

DECLARE loccur CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT name, SCHEMA_NAME(schema_id), base_object_name FROM sys.synonyms
OPEN loccur

FETCH NEXT FROM loccur INTO @ObjectName, @Schema, @Definition

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Converting: Synonym, ' + @ObjectName

    SET @SQL = 'DROP SYNONYM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@ObjectName)
    EXEC(@SQL)

    SET @SQL = 'CREATE SYNONYM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@ObjectName) + ' FOR ' +
                  REPLACE(@Definition, '[OldDbName].', '[NewDbName].')
    EXEC(@SQL)

    FETCH NEXT FROM loccur INTO @ObjectName, @Schema, @Definition
END

CLOSE loccur
DEALLOCATE loccur

Enjoy!

Follow me on twitter @sqlserverrocks

Subscribe to my blog RSS feed

Comment on this or any of my posts or contact me directly from http://www.olcot.co.uk