I created the following into a stored procedure so that I can run it again and again:
DECLARE @dynsqlcmd VARCHAR(250)
DECLARE @tblname VARCHAR(150)
DECLARE @synname VARCHAR(150)
DECLARE @cnter INT
-- Drop all synonyms
DECLARE dbcursor CURSOR FOR
SELECT sysobjects.name AS 'table name'
FROM sysobjects
WHERE sysobjects.type = 'sn'
OPEN dbcursor
FETCH NEXT
FROM dbcursor
INTO @synname
WHILE (@@FETCH_STATUS =0)
BEGIN
SET @dynsqlcmd = 'DROP SYNONYM ' + @synname
EXEC (@dynsqlcmd)
FETCH NEXT
FROM dbcursor
INTO @synname
END
CLOSE dbcursor
DEALLOCATE dbcursor
-- Create Synonym
DECLARE tblnmcursor CURSOR FOR
SELECT sysobjects.name AS 'table name'
FROM CMSQNXTTESTSQL.PlanData_Dev.dbo.sysobjects
WHERE sysobjects.name <> 'dtproperties'
AND sysobjects.name <> 'sysdiagrams'
AND sysobjects.type IN ('u','v')-- No SPs
OPEN tblnmcursor
FETCH NEXT
FROM tblnmcursor
INTO @tblname
WHILE (@@FETCH_STATUS =0)
BEGIN
SET @synname = 'Any Prefix' + @tblname
SET @dynsqlcmd = 'CREATE SYNONYM ' + @synname + ' FOR [Server Name].[Database Name].dbo.' + @tblname
EXEC (@dynsqlcmd) --PRINT (@dynsqlcmd) --
FETCH NEXT
FROM tblnmcursor
INTO @tblname
END
CLOSE tblnmcursor
DEALLOCATE tblnmcursor