Create synonym for all tables from another server or linked server object

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

Print | posted on Thursday, September 24, 2009 1:48 PM

Feedback

# re: Create synonym for all tables from another server or linked server object

Left by Jenni Allen at 1/6/2016 4:19 PM
Gravatar Thank you for posting this! Works great!

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski