Edit synonyms in MS SQL Server 2005

Question

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.

Answer 1

There is no

ALTER SYNONYM

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

Answer 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