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