10

Out of curiousity, is there any way to edit an existing synonym? That is, change which table the synonym is pointing to...

Thus far I seem to have had to delete and re-create them, because they're locked from being edited. It's not a big deal, but at the same time it's a little irritating.

GUI or scripting, but preferably GUI.

Margaret
  • 5,749
  • 20
  • 56
  • 72
  • Scripting the DROP/CREATE is the only approach I've found that works. :-\ – deadbug Jul 03 '09 at 01:06
  • As a point of interest, some Googling pulled up http://www.sqlmaestro.com/products/mssql/maestro/help/03_09_00_synonyms/ - it looks like there are third-party products that allow it. Whether they're worth the purchase price is a different issue... – Margaret Jul 03 '09 at 02:35
  • 2
    Ironic that a synonym [*"Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object"*](http://msdn.microsoft.com/en-us/library/ms187552.aspx), and you cannot change the synonym when changes happen. You need a synonym for your synonym to protect your synonym from changes to it's synonym. – Ian Boyd May 30 '13 at 15:18

3 Answers3

13

There is no 

ALTER SYNONYM

You have to drop and recreate the synonym. See this article.

jn29098
  • 1,405
  • 1
  • 15
  • 25
2

Unfortunately there is no ALTER SYNONYM. There is however a feedback in Microsoft Connect asking for this functionality. The reply from Microsoft is not very clear though. They talk about some added functionality in katmai, but I am not able to understand that. Check this link

I think synonyms are vastly under-rated and under-utilized. Consider this scenario. You are running SQL Express with a limit of 4 GB per database. When your DB is almost at 4 GB, just move the larger tables to another DB and create a synonym in the original DB and you have effectively increased your DB size to beyond 4 GB.

Though the answer to the OP's question is NO, just thought I should share this with the community to benefit those of us using SQL express and are stumped with the 4 GB limit.

Raj

Raj
  • 10,653
  • 2
  • 45
  • 52
0

Drop & recreate the synonym

Ex:

1.After you drop the existing synonym

2.USE 'Your DB'

GO

CREATE SYNONYM 'Your synonym name' FOR 'Synonym for which table'

GO

coder kemp
  • 361
  • 2
  • 13