SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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 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

SQL Server Rocks!

SQL Database Administrator/Developer. Background in developing OLTP/document based databases, SQLXML and performance tuning with an unhealthy fascination for the SQL query optimiser!

Comments

Leave a comment on the original post [www.olcot.co.uk, opens in a new window]

Loading comments...