DECLARE @counter int,
		@i int = 1,
		@syn_name NVARCHAR(100),
		@suffix NVARCHAR(10),
		@sql NVARCHAR(256);

BEGIN

	IF NOT EXISTS(SELECT * FROM stagingtable)
	BEGIN
		PRINT 'STAGINGTABLE not migrated. Please check'
		RETURN
	END

	IF NOT EXISTS(SELECT * FROM sys.synonyms where is_ms_shipped = 0)
	BEGIN
		PRINT 'Synonyms not migrated. Please check'
		RETURN
	END

	DROP TABLE IF EXISTS #temptable

	CREATE TABLE #temptable 
	(
		id int IDENTITY(1, 1), 
		synonym_name NVARCHAR(100), 
		suffix NVARCHAR(10)
	)

	INSERT INTO #temptable
	SELECT s.name as synonym_name, st.LIVETABLENAMESUFFIX as suffix
	FROM stagingtable st 
		JOIN sys.synonyms s ON (st.TABLENAME = s.name COLLATE database_default)
	WHERE RIGHT(PARSENAME(s.base_object_name, 1), 2) != st.LIVETABLENAMESUFFIX

	SELECT @counter = count(1) from #temptable

	if @counter = 0
	BEGIN
		PRINT 'No differences found between SYS.SYNONYMS and STAGINGTABLE'
	END
	ELSE
	BEGIN
		PRINT 'Differences found between SYS.SYNONYMS and STAGINGTABLE. Fix them...'

		WHILE @i <= @counter
		BEGIN
			SELECT @syn_name = synonym_name, @suffix = suffix from #temptable where id = @i

			PRINT 'Reset synynom ''' + @syn_name + ''''

			SET @sql = CONCAT('DROP SYNONYM ', @syn_name)
			EXECUTE sp_executesql @sql

			SET @sql = CONCAT('CREATE SYNONYM ', @syn_name, ' FOR ', dbo.STAGING$get_table_name(@syn_name, @suffix))
			EXECUTE sp_executesql @sql

			SET @i = @i + 1
		END
		
		PRINT 'Synonyms fixed.'
	END

	DROP TABLE IF EXISTS #temptable

END