Drop and Recreate all Synonyms

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 FORWARDONLY READONLY FOR 
SELECT name, SCHEMANAME(schemaid), baseobjectname 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