/*
	After taking over the synonyms from Oracle to SQL-Server in a SSMA migration
	The Synonyms on SQL server have a format like:
		[DBName].[schema].[table] i.e.:
		[icmdbMSLive].[dbo].[D$USERGROUPASSIGNMENT$2]
	This will cause Issues if the DB will be re-imported into an DB with an other name.
	
	This script renames the targets of the synonyms 
	from [DBName].[schema].[table]  to [table].
*/

DECLARE @name SYSNAME;
DECLARE @sboname SYSNAME;
DECLARE @stmt NVARCHAR(MAX);
DECLARE c CURSOR FOR 
SELECT 
  name, 
  SUBSTRING(base_object_name, LEN(base_object_name) - CHARINDEX('.', REVERSE(base_object_name)) + 2, LEN(base_object_name) - 1) sboname
FROM sys.synonyms s;

OPEN c 
FETCH NEXT FROM c INTO @name, @sboname

WHILE @@FETCH_STATUS = 0  
BEGIN  
  IF @sboname != ''
  BEGIN
    SET @stmt = CONCAT('DROP SYNONYM ', @name)
    EXECUTE sp_executesql @stmt
    
    SET @stmt = CONCAT('CREATE SYNONYM ', @name, ' FOR ', @sboname)
	PRINT @stmt
    EXECUTE sp_executesql @stmt
  END 
  
  FETCH NEXT FROM c INTO @name, @sboname 
END 

CLOSE c  
DEALLOCATE c