T-SQL: Script all Synonyms for a Database

If you have synonyms in a database that point to a database that you need to move or rename, you will need to update those synonyms as part of your update.

Here’s a script that generates DROP/CREATE statements for all of the synonyms in a SQL database:

USE MyDatabase
GO
 
DECLARE @OldDbName VARCHAR(50) = 'OldDB'
DECLARE @NewDbName VARCHAR(50) = 'NewDB'
 
DECLARE @ObjectName sysname, @Definition VARCHAR(MAX), @Schema VARCHAR(50) 
DECLARE @SQL VARCHAR(MAX) 
 
DECLARE synonymCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
SELECT name, SCHEMA_NAME(schema_id), base_object_name FROM sys.synonyms 
 
OPEN synonymCursor 
FETCH NEXT FROM synonymCursor INTO @ObjectName, @Schema, @Definition 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
  SET @SQL = 'DROP SYNONYM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@ObjectName) + '
GO
'
  PRINT @SQL
 
  SET @SQL = 'CREATE SYNONYM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@ObjectName) + ' FOR ' + 
                  REPLACE(@Definition, (QUOTENAME(@OldDbName) + '.'), (QUOTENAME(@NewDbName) + '.')) + '
GO
 
' 
    PRINT @SQL
 
    FETCH NEXT FROM synonymCursor INTO @ObjectName, @Schema, @Definition 
END 
 
CLOSE synonymCursor 
DEALLOCATE synonymCursor

Hope this helps!

This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *